Search code examples
mysqlsqlmysql-5.7

Put all values for each id in different column

I have a table emp as

id|  doc |request_type
1 |  6   |  2
1 |  2   |  2
1 |  1   |  2
1 |  3   |  2
2 |  6   |  1
2 |  4   |  1
3 |  6   |  4
3 |  1   |  4
3 |  5   |  4

And I am trying to put all the doc values for each id in a new column.

Desired output

id|  doc     | request_id
1 |  6,2,1,3 |  2
2 |  6,4     |  1
3 |  6,1,5   |  4
Solution

  • use group_concat

    select id, group_concat(doc) as doc, request_id from <table> group by id,request_id

    for more information read https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat

    As @MatBailie mentioned, it is ok for selects, but you should not insert it into another column