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)
