欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

MySQL – Grouping by Multiple Columns to Single Column as A_MySQL

程序员文章站 2022-06-08 17:13:19
...
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));
INSERT INTO TestTable (ID, Col)
SELECT 1, 'A'
UNION ALL
SELECT 1, 'B'
UNION ALL
SELECT 1, 'C'
UNION ALL
SELECT 2, 'A'
UNION ALL
SELECT 2, 'B'
UNION ALL
SELECT 2, 'C'
UNION ALL
SELECT 2, 'D'
UNION ALL
SELECT 2, 'E';

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

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

MySQL – Grouping by Multiple Columns to Single Column as A_MySQL

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
GROUP BY ID;

MySQL – Grouping by Multiple Columns to Single Column as A_MySQL

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)