• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

Display Rows as columns using MS SQL 2005

I  am looking for a way to present rows as columns in sql.

To give context, it is for a tour booking system, a report to show number of passengers booked on a different tours, displayed by category.

I have 2 tables...

Table 1,
name: reservation
fields: id, tourid, tourdate

Table2,
name: passengernumbers
fields: id, reservationid, passengercategory, numberofpassengers

At the moment, I only know how to get the following...

select * from reservation inner join passengernumbers on reservation.id=passengernumbers.id

this returns a row for each category of passenger whereas i only want one row for each reservation id and to have each passengercategory as a column.

e.g.

At present I have.

reservation.id   passengernumbers.categoryid  passengernumbers.numberofpassengers
         1                                   Adult                                                    2                
         1                                   Child                                                     4
         1                                   Under 2                                                1
         2                                   Adult                                                    3                
         2                                   Child                                                     3
         2                                   Under 2                                                4

What i need is...

reservation.id    Adult     Child   Under2
       1                    2           4          1
       2                    3           3          4

Do you know how this can be achieved?
0
alanrogerson2
Asked:
alanrogerson2
  • 2
1 Solution
 
Patrick MatthewsCommented:
SELECT DISTINCT r.id,
    (SELECT SUM(p.numberofpassengers) FROM passengernumbers p WHERE p.reservationid = r.id AND p.passengercategory = 'Adult') AS Adult,
    (SELECT SUM(p.numberofpassengers) FROM passengernumbers p WHERE p.reservationid = r.id AND p.passengercategory = 'Child') AS Child,
    (SELECT SUM(p.numberofpassengers) FROM passengernumbers p WHERE p.reservationid = r.id AND p.passengercategory = 'Under 2') AS [Under 2]
FROM reservation r
ORDER BY r.id
0
 
Sham HaqueSenior SAP CRM ConsultantCommented:
the standard way to crosstab a resultset is using SUM(CASE....

SELECT r.ID
             ,SUM(CASE WHEN p.passengercategory = 'Adult' THEN p.numberofpassengers ELSE 0 END) as Adult
             ,SUM(CASE WHEN p.passengercategory = 'Child' THEN p.numberofpassengers ELSE 0 END) as Child
             ,SUM(CASE WHEN p.passengercategory = 'Under 2' THEN p.numberofpassengers ELSE 0 END) as [Under 2]
FROM reservation r
JOIN passengernumbers p
ON p.reservationid = r.id
0
 
Sham HaqueSenior SAP CRM ConsultantCommented:
sorry - forgot the group by....

SELECT r.ID
             ,SUM(CASE WHEN p.passengercategory = 'Adult' THEN p.numberofpassengers ELSE 0 END) as Adult
             ,SUM(CASE WHEN p.passengercategory = 'Child' THEN p.numberofpassengers ELSE 0 END) as Child
             ,SUM(CASE WHEN p.passengercategory = 'Under 2' THEN p.numberofpassengers ELSE 0 END) as [Under 2]
FROM reservation r
JOIN passengernumbers p
ON p.reservationid = r.id
GROUP BY r.ID
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now