SQL如何基于parentid字段将多行数据转置为单行展示

这个需求属于分组字符串聚合，不同数据库对应的内置聚合函数不同，以下是主流数据库的实现方式，假设你的表名为t_example，包含id、name、parentid三个字段：

MySQL 实现 #

MySQL用GROUP_CONCAT函数，默认分隔符是逗号，支持自定义分隔符：

SELECT 
  parentid,
  GROUP_CONCAT(id ORDER BY id SEPARATOR ',') AS ids,
  GROUP_CONCAT(name ORDER BY id SEPARATOR ',') AS names
FROM t_example
GROUP BY parentid;

注意：如果合并的内容太长超过默认长度限制，可以先执行SET group_concat_max_len = 102400;调整最大长度阈值。

PostgreSQL 实现 #

PostgreSQL用STRING_AGG函数：

SELECT 
  parentid,
  STRING_AGG(id::VARCHAR, ',' ORDER BY id) AS ids,
  STRING_AGG(name, ',' ORDER BY id) AS names
FROM t_example
GROUP BY parentid;

SQL Server 实现 #

SQL Server 2017及以上版本支持STRING_AGG：

SELECT 
  parentid,
  STRING_AGG(id, ',') WITHIN GROUP (ORDER BY id) AS ids,
  STRING_AGG(name, ',') WITHIN GROUP (ORDER BY id) AS names
FROM t_example
GROUP BY parentid;

低版本SQL Server可以用FOR XML PATH语法实现：

SELECT 
  t1.parentid,
  STUFF((SELECT ',' + CAST(id AS VARCHAR(10)) FROM t_example t2 WHERE t2.parentid = t1.parentid ORDER BY id FOR XML PATH('')),1,1,'') AS ids,
  STUFF((SELECT ',' + name FROM t_example t2 WHERE t2.parentid = t1.parentid ORDER BY id FOR XML PATH('')),1,1,'') AS names
FROM t_example t1
GROUP BY t1.parentid;

Oracle 实现 #

Oracle 11gR2及以上版本用LISTAGG函数：

SELECT 
  parentid,
  LISTAGG(id, ',') WITHIN GROUP (ORDER BY id) AS ids,
  LISTAGG(name, ',') WITHIN GROUP (ORDER BY id) AS names
FROM t_example
GROUP BY parentid;

内容的提问来源于stack exchange，提问作者unnikrishnan