Jason247 Jason247 - 20 days ago 5
MySQL Question

MYSQL Group_Concat values into one field but keep multiple rows

I have two tables in MySQL, one containing a single value in each row and the other containing the same values, but they are all combined into a single row. So for example:

Table1:

Column1
a
b


Table2:

Column1
a,b


I would like to make a mapping table based off of table 1 so that I can do a join on them. The result I am looking for would be this:

Column1 | Column2
a | a,b
b | a,b


I have tried this:

SELECT Column1, GROUP_CONCAT(DISTINCT Column1) AS Column2
FROM Table1


But it just returns a single row:

Column1 | Column2
a | a,b


I have also tried adding a group by clause:

SELECT Column1, GROUP_CONCAT(DISTINCT Column1) AS Column2
FROM Table1
GROUP BY Column1


This returns each row, but doesn't concatenate the fields into column2:

Column1 | Column2
a | a
b | b


Is there any way to solve this? I appreciate the help!

Answer

You need to join the table with a subquery that returns the concatenated value.

SELECT t1.Column1, g.c
FROM Table1 AS t1
CROSS JOIN (
    SELECT GROUP_CONCAT(Column1) AS c
    FROM Table1) AS g