MySQL–GroupingbyMultipleColumnstoSingleColumnasA_MySQL

来源：动视网责编：小采时间：2020-11-09 19:16:04

MySQL–GroupingbyMultipleColumnstoSingleColumnasA_MySQL

MySQL–GroupingbyMultipleColumnstoSingleColumnasA_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 gr

推荐度：

点击下载本文 文档为doc格式

导读MySQL–GroupingbyMultipleColumnstoSingleColumnasA_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 gr

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;

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;

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)

MySQL–GroupingbyMultipleColumnstoSingleColumnasA_MySQL

推荐度：

点击下载本文 文档为doc格式

标签： mysql column mysql–groupingbymultiplecolumnstosinglecolumnasa_mysql

热门焦点

MySQL–GroupingbyMultipleColumnstoSingleColumnasA_MySQL

MySQL–GroupingbyMultipleColumnstoSingleColumnasA_MySQL

MySQL–GroupingbyMultipleColumnstoSingleColumnasA_MySQL

最新推荐

猜你喜欢

热门推荐