alanrogerson2
asked on
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=passengernu
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.categoryi
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?
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=passengernu
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.categoryi
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?
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
(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