MySQL: one column/multiple rows group_concat into multiple columns filled with values from another column

Question

I've seen many answers to group_concat one column/multiple rows into multiple columns, but I cannot find how you populate the new columns with data from a second column.

My table of data looks like this:

first_name  doc_number  doc_status
a              1           new
a              2           new
b              3           pending
b              4           approved
c              5           new
c              6           approved
c              7           approved

This is the result I would like:

first_name   doc_status_new   doc_status_pending   doc_status_approved
    a             1,2
    b                                3                      4
    c              5                                       6,7

This is the result I get:

    first_name   doc_status_new   doc_status_pending   doc_status_approved
    a             1,2
    b                                3,4                      
    c             5,6,7

Row 2 shouldn't have '3,4' in the same column. And Row 3 shouldn't have "5,6,7" in the same column either. I do not know how to get group_concat to only list the doc_number that is relevant to that 'where' statement only.

This is the code I've used:

select 
first_name,
case when doc_status = 'new' then group_concat(doc_number) end as doc_status_new,
case when doc_status = 'pending' then group_concat(doc_number) end as doc_status_pending,
case when doc_status = 'approved' then group_concat(doc_number) end as doc_status_approved
from table_name
group by first_name;

Ankit Bajpai · Accepted Answer · 2020-01-20 18:11:28Z

1

You probably need -

select first_name,
       group_concat(case when doc_status = 'new' then doc_number end) as doc_status_new,
       group_concat(case when doc_status = 'pending' then doc_number end) as doc_status_pending,
       group_concat(case when doc_status = 'approved' then doc_number end) as doc_status_approved
from table_name
group by first_name;

answered Jan 20, 2020 at 18:11

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

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

Comments

juanram0n · Accepted Answer · 2020-01-20 18:14:51Z

0

I think the better solution is using UNION, it is very fast, and using LIKE:

(SELECT group_concat(doc_number) AS doc_status_new WHERE doc_status LIKE 'new')
UNION
(SELECT group_concat(doc_number) AS doc_status_pending WHERE doc_status LIKE 'pending')
UNION
(SELECT group_concat(doc_number) AS doc_status_approved WHERE doc_status LIKE 'approved')

I hope this help.

answered Jan 20, 2020 at 18:14

juanram0n

3563 silver badges23 bronze badges

1 Comment

Bill Karwin Over a year ago

This won't produce what the OP wanted. They want one row per first_name, not one row per distinct doc_status.

Collectives™ on Stack Overflow

MySQL: one column/multiple rows group_concat into multiple columns filled with values from another column

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related