Get mysql column values to row

Question

I have a mysql table for bookings

Bookingid  Customer

  1         Steve
  2         John

and another table for passengers

Bookingid  Passenger

  1        Steve
  1        Gary
  1        Tom
  2        John
  2        Chris
  2        Thomas

What is the best query to get bookingid from the first table and passengers from the second table, in a single row ?

Bookingid  Passenger1 Passenger2 Passenger3
  1         Steve       Gary      Tom
  2         John        Chris     Thomas

So, look at my given answer. You need a PIVOT TABLE. Which is, in my opinion, painfull to do directly over mysql. — Jorge Campos, Nov 14 '13 at 19:18

score 0 · Accepted Answer · edited May 23 '17 at 11:50

You can´t do this without a PIVOT TABLE Wich in most cases has fixed numbers of columns to rows.

This one has a procedure to do it automatticaly http://www.artfulsoftware.com/infotree/qrytip.php?id=523

But you have a function on MySql wich will give you something to work with. You will not see the Passenger1..PassengerN you will see a result like this:

1 Steve, Gary, Tom
2 John, Chris, Thomas

If that is good enough to you this is your query:

select passengers.Bookingid, group_concat(bookings.Customer)
  from bookings inner join passengers on ( bookings.Bookingid = passengers.Bookingid )
group by passengers.Bookingid

Get mysql column values to row

1 Answers1