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)
每个人都需要一台速度更快、更稳定的 PC。随着时间的推移，垃圾文件、旧注册表数据和不必要的后台进程会占用资源并降低性能。幸运的是，许多工具可以让 Windows 保持平稳运行。
Copyright 2014-2025 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号
401
收藏
取消收藏
