Multiple rows values to one cell in MySQL

Question

I would like to ask for your help in order to achieve the following I have a table that the id column may have the same id on multiple rows which corresponds to different values on another column like the example bellow

TABLE #1

+-----+---------+
| id1 | value1  | 
+-----+---------+
|  1  |    1    | 
+-----+---------+
|  1  |    2    | 
+-----+---------+
|  1  |    3    | 
+-----+---------+
|  2  |    4    | 
+-----+---------+
|  2  |    5    | 
+-----+---------+
|  2  |    6    | 
+-----+---------+

Based on id column I need the values that corresponds to the same id to be inputted in the same cell separated by a comma like the following example

Those need to be updated on another table

TABLE #2

+-----+---------+
| id2 | value2  | 
+-----+---------+
|  1  |  1,2,3  | 
+-----+---------+
|  2  |  4,5,6  | 
+-----+---------+

How can this be done?

use `group_concat()` to achieve this in a result set but never save your data this way. See [Junction Tables](http://stackoverflow.com/a/32620163) if you are ever entertaining the idea of doing so — Drew, Sep 17 '16 at 20:56
Possible duplicate of [Can I concatenate multiple MySQL rows into one field?](http://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field) — Drew, Sep 17 '16 at 21:16

Kamil Gosciminski · Accepted Answer · 2016-09-17T20:59:05.770

1

What is the reason for denormalizing your data and violating First Normal Form (1NF)? You shouldn't do that - it's better to have the data normalized. You could create a view if it's required to present data this way.

Anyways, since this answer should also include a solution, here's one:

Use GROUP_CONCAT() aggregate function:

SELECT id1, GROUP_CONCAT(value1 ORDER BY value1 ASC SEPARATOR ',')
FROM table1
GROUP BY id1

To update results in other table where ids match:

UPDATE table2 a
INNER JOIN ( 
  SELECT id1, GROUP_CONCAT(value1 ORDER BY value1 ASC SEPARATOR ',') AS value2
  FROM table1
  GROUP BY id1
  ) b ON a.id1 = b.id2
SET a.value2 = b.value2

edited Sep 17 '16 at 20:59

answered Sep 17 '16 at 20:53

Kamil Gosciminski

16,547
8
49
72

A valid reason for denormalizing like this could be the use of paging in an application. Example: I want to see results by 'id1' in the query above - 20 records at a time. – NEW2WEB Jun 24 '22 at 18:27

score 0 · Answer 2 · answered Sep 17 '16 at 20:53

0

you can use group_concat

select id, group_concat(value1) as value2
from table1
group by id

answered Sep 17 '16 at 20:53

ScaisEdge

131,976
10
91
107

Multiple rows values to one cell in MySQL

2 Answers2

Linked

Related