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.

fthiella · Accepted Answer · 2013-04-04 19:04:57Z

5

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.

edited Apr 4, 2013 at 19:04

answered Apr 4, 2013 at 18:59

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Zimzalabim Over a year ago

Great. This works nice. The use of COLUMN_NAME makes it even better.

Explosion Pills · Accepted Answer · 2013-04-04 18:50:37Z

3

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, ', '')

edited Apr 4, 2013 at 18:50

answered Apr 4, 2013 at 18:39

Explosion Pills

192k56 gold badges341 silver badges417 bronze badges

2 Comments

Zimzalabim Over a year ago

Yes. Considered that, only with 10 columns it started to look very ugly ;)

Zimzalabim Over a year ago

Thanks. I need to have multiple REPLACE statements though, (or find another clever way to remove all REMOVEIT's), I'm looking at it.

Slowcoder · Accepted Answer · 2013-04-04 19:10:57Z

1

Try this.

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

Here is the mysql documentation.

http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_trim

answered Apr 4, 2013 at 19:10

Slowcoder

2,1303 gold badges17 silver badges21 bronze badges

2 Comments

Zimzalabim Over a year ago

Thank you. Though this does not work too good with 3+ columns it is valuable information.

Slowcoder Over a year ago

If the schema is in your control, I would suggest you to put the column names as values in a separate table, and use the foreign key reference in the origin table to simplify things. This will also allow you to add more values on the fly by just inserting rows instead of adding more columns to the origin table.

Collectives™ on Stack Overflow

MySql: Concat IF'ed values

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related