MySql: Concat IF'ed values

Question

Trying to find a way to combine CONCAT with IF – or in some other way display textual value for set flags in a comma separated list.

Note: This is eventually for a table with 10+ columns. Using two here to simplify.

Say I have a table with flags. If flag is 1 I want to display some textual value, else nothing.

origin:
+--+-----+-----+
|# | CHS | ACC |
+--+-----+-----+
|1 | 0   | 1   |
|2 | 1   | 1   |
|3 | 1   | 0   |
|4 | 0   | 0   |
+--+-----+-----+

I want:

+--+----------+
|# |  origin  |
+--+----------+
|1 | ACC      |
|2 | CHS, ACC |
|3 | CHS      |
|4 |          |
+--+----------+

Not (this or the like):

+--+-----------+
|# | origin    |
+--+-----------+
|1 | ACC,      |
|2 | CHS, ACC, |
|3 | CHS,      |
|4 | ,         |
+--+-----------+

Something in the direction of this:

SELECT
    CONCAT_WS(', ',
        IF(CHS = 0, '', 'CHS'),
        IF(ACC = 0, '', 'ACC')
    ) as origin
FROM
    origin;

But not with comma between empty values.

This is eventually for a table with 10 columns that I join with other table based on id.

Answer 1

You could use CONCAT_WS, that skips NULL values, in combination with CASE:

SELECT
  id,
  CONCAT_WS(', ',
    CASE WHEN CHS THEN 'CHS' END,
    CASE WHEN ACC THEN 'ACC' END
  ) as origin
FROM
  origin;

When the condition is FALSE, since I am not specifying an ELSE clause, CASE will return a NULL value, and CONCAT_WS will skip it. You could also use NULL on your original query instead of ''.

If you have many columns, you can also create a dynamic query with something like this:

SELECT
  CONCAT(
    'SELECT ID, CONCAT_WS(\', \',',
    GROUP_CONCAT(
      CONCAT(
        'CASE WHEN ',
        `COLUMN_NAME`,
        ' THEN \'',
        `COLUMN_NAME`,
        '\' END') SEPARATOR ','),
    ') AS origin FROM origin;'
    )
FROM
  `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE
  `TABLE_NAME`='origin'
  AND COLUMN_NAME!='id'
INTO @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;

Please see fiddle, with both solutions, here.

Answer 2

There may be a nicer looking solution, but you can use

CONCAT(
    IF(CHS = 0, '', 'CHS'),
    IF(CHS != 0 AND ACC != 0, ', ', ''),
    IF(ACC = 0, '', 'ACC')
)

You could also use REPLACE to remove the "blanks:"

REPLACE(CONCAT_WS(', ',
    IF(CHS = 0, 'REMOVEIT', 'CHS'),
    IF(ACC = 0, 'REMOVEIT', 'ACC')
), 'REMOVEIT, ', '')