mySQLi显示列，列数据不是基于行的NULL

Question

Book Table

bookID    tripNo
b01         t01
b02         t02
b03         t03

Trip Table

tripNo  bookID  seat1     seat1    seat2    seat3  seat4    seat5    seat6     seat7    seat8    seat9    seat10    seat11    seat11
t01      b01     booked   booked   NULL     NULL   booked   booked   booked     booked  NULL    NULL        NULL    booked      booked  
t02      b02     NULL     booked    NULL    NULL   booked   NULL     booked     NULL    NULL    booked      NULL    booked      booked  
t03      b03     booked   booked   NULL     booked   booked   booked   booked   booked  NULL    booked      booked  booked      booked  

how do i make it like show all the seat that is not NULL?

Expected Result

bookID   Booked
b01       seat1,seat2,seat5,seat6,seat7,seat12,seat13
b02       seat2,seat5,seat7,seat10,seat12,seat13

Answer 1

A SQL query returns a fixed set of columns, so you cannot conditionally remove a column. So, you can't really do what you want, unless you resort to dynamic SQL.

Your trip table has an awkward format. You should really have a table TripSeats with one row per trip and per seat.

If each trip has exactly 11 seats, then you can create them when the trip is created -- just populate them with NULL values.

If you did this, it would be very easy to extract the information you want.