C.S C.S - 2 months ago 5
MySQL Question

Multiple rows values to one cell in MySQL

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?

Answer

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
Comments