MySQL – Grouping by Multiple Columns to Single Column as A_MySQL

In this post titledSQL SERVER – Grouping by Multiple Columns to Single Column as A Stringwe have seen how to group multiple column data in comma separate values in a single row grouping by another column by using FOR XML clause.

In this post we will see how we can produce the same result usingthe GROUP_CONCAT functionin MySQL.

Let us create the following table and data.

CREATE TABLE TestTable (ID INT, Col VARCHAR(4));<br> INSERT INTO TestTable (ID, Col)<br> SELECT 1, 'A'<br> UNION ALL<br> SELECT 1, 'B'<br> UNION ALL<br> SELECT 1, 'C'<br> UNION ALL<br> SELECT 2, 'A'<br> UNION ALL<br> SELECT 2, 'B'<br> UNION ALL<br> SELECT 2, 'C'<br> UNION ALL<br> SELECT 2, 'D'<br> UNION ALL<br> SELECT 2, 'E';<br>

Now to generatecsvvalues of the column col for each ID, use the following code

SELECT ID, GROUP_CONCAT(col) AS CSV FROM TestTable<br> GROUP BY ID;

The result is

ID CSV 1 A,B,C 2 A,B,C,D,E

You can also change the delimiters. Forexample insteadof comma, if you want to have a pipe symbol (|), use the following

SELECT ID, REPLACE(GROUP_CONCAT(col),',','|') AS CSV FROM TestTable<br> GROUP BY ID;

The result is

ID CSV 1 A|B|C 2 A|B|C|D|E

MySQL makes this very simple withitssupport of GROUP_CONCAT function.

Reference: Pinal Dave (http://blog.sqlauthority.com)