Ziaur Rahman Ziaur Rahman - 8 months ago 55
MySQL Question

how to select different columns with same value in a row of a table?

the table would be as below: table name = 'seats'

seat1 | seat2 | seat3 | seat4 | seat5
0 | 1 | 1 | 0 | 1


I need an sql query whose output will be

seat2, seat3, seat5

Answer Source

As per my understanding you are looking fetch only seats which are occupied. I have prepared the following query based on that understanding. Please comment if gap in understanding is there :

select * from 
(
select 'seat1', seat1 as occupiedStatus from [table]
union
select 'seat2', seat2 as occupiedStatus from [table]
union
select 'seat3', seat3 as occupiedStatus from [table]
union
select 'seat4', seat4 as occupiedStatus from [table]
union
select 'seat5', seat5 as occupiedStatus from [table]
) seatsPivot
where occupiedStatus = 1 ;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download