ホームページ >データベース >mysql チュートリアル >MySQL – Grouping by Multiple Columns to Single Column as A_MySQL

MySQL – Grouping by Multiple Columns to Single Column as A_MySQL

WBOY
WBOYオリジナル
2016-06-01 13:14:371156ブラウズ

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)

声明:
この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。