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
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.