MySql: Concat IF'ed values

mysql

Solution

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.

Problem

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.

Original source