Aman Aman - 5 months ago 9
SQL Question

Group concat the column names from the table based on their row values

I have a table with multiple field names whose value in the rows can be 'no'/Null/'yes'. Now I need to get the concatenated field names of all the fields whose row value is 'no'.

Consider my table

id
field1
field2
field3


1
no
yes
no


2
yes
no
no


Then I need the output though MySQL as

id
fields


1
field1,field3


2
field2,field3


Is it possible?

Answer

The solution using CONCAT_WS function(will return a concatenated string containing only those fields where values are 'no'):

SELECT 
    id, 
    CONCAT_WS(',',
              IF(`field1` = 'no', 'field1', null),
              IF(`field2` = 'no', 'field2', null),
              IF(`field3` = 'no', 'field3', null)
    ) AS fields
FROM  `your_table`

CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.

http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_concat-ws

Comments