MySQL conditional concatenate based on multiple columns

Question

I'm stumped on a proper query to get an output based on multiple columns and the conditions in those columns:

   a  b  c
1  x     x
2  x  x  
3  x

I would like the results to output, based on where the x's are in columns a, b and c:

1 a,c
2 a,b
3 a

Is this possible to do in mysql? Much appreciated

Jo. · Accepted Answer · 2017-05-23 17:16:35Z

1

You can use the CONCAT_WS function (docs) and some IF statements.

SELECT tn.ID,
CONCAT_WS(
  ',',
  IF(tn.a='x','a',null),
  IF(tn.b='x','b',null),
  IF(tn.c='x','c',null)
) as result
FROM TableName tn;

answered May 23, 2017 at 17:16

Jo.

7901 gold badge12 silver badges18 bronze badges

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

1 Comment

DarbyM Over a year ago

I just leaned something.. Never used CONCAT_WS before.. this is definitely a good use of it.

naffiq · Accepted Answer · 2017-05-23 17:01:21Z

0

You can use IFNULL function for that (docs). For example:

SELECT a, IFNULL(b, c) FROM table_name;

It will select a for every case and conditionally b or c, depending on it's value (it have to be not null). But I'm afraid you cannot do more than that.

answered May 23, 2017 at 17:01

naffiq

1,0701 gold badge9 silver badges19 bronze badges

Collectives™ on Stack Overflow

MySQL conditional concatenate based on multiple columns

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related