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...
fields: id, tourid, tourdate
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.
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?