user3090790 user3090790 - 2 years ago 60
SQL Question

convert certain column names with comma separated string from sql table with conditions

For example , I have this table with different column names and the Boolean value below it,

case1 case2 case3 case4
1 0 1 0

What I want to retrieve,only column names with 1 value. So, my desired results from the query should only be case1,case3

Desired Output : case1,case3

there is only one row fetch from sql query

Is there any way?

Answer Source

If I understand correctly, you could use a big case statement:

select stuff(( (case when case1 = 1 then ',case1' else '' end) +
               (case when case2 = 1 then ',case2' else '' end) +
               (case when case3 = 1 then ',case3' else '' end) +
               (case when case4 = 1 then ',case4' else '' end)
             ), 1, 1, '') as columns
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download