Oracle中如何将多行分组名称合并为单行显示

首先修正你原查询里的小问题：Groups是客户与组的关联表，本身没有Name字段，组名存储在GroupDefinitions里，所以正确的基础关联查询应该是：

SELECT c.cID, gd.Name
FROM Customer c
LEFT JOIN Groups g ON c.cID = g.cID
LEFT JOIN GroupDefinitions gd ON gd.gID = g.gID;

下面根据不同需求场景给出解决方案：

1. 简单通用：将组名合并为单个分隔符列 #

如果不需要严格拆分到独立列，只是把同一客户的所有组名放在一行的同一列（用逗号/分号分隔），所有主流数据库都支持这种方式：

MySQL/MariaDB #

SELECT 
  c.cID,
  GROUP_CONCAT(DISTINCT gd.Name SEPARATOR ', ') AS group_names
FROM Customer c
LEFT JOIN Groups g ON c.cID = g.cID
LEFT JOIN GroupDefinitions gd ON gd.gID = g.gID
GROUP BY c.cID;

PostgreSQL #

SELECT 
  c.cID,
  STRING_AGG(DISTINCT gd.Name, ', ') AS group_names
FROM Customer c
LEFT JOIN Groups g ON c.cID = g.cID
LEFT JOIN GroupDefinitions gd ON gd.gID = g.gID
GROUP BY c.cID;

SQL Server（2017+） #

SELECT 
  c.cID,
  STRING_AGG(DISTINCT gd.Name, ', ') AS group_names
FROM Customer c
LEFT JOIN Groups g ON c.cID = g.cID
LEFT JOIN GroupDefinitions gd ON gd.gID = g.gID
GROUP BY c.cID;

Oracle #

SELECT 
  c.cID,
  LISTAGG(DISTINCT gd.Name, ', ') WITHIN GROUP (ORDER BY gd.Name) AS group_names
FROM Customer c
LEFT JOIN Groups g ON c.cID = g.cID
LEFT JOIN GroupDefinitions gd ON gd.gID = g.gID
GROUP BY c.cID;

2. 固定列数：将组名拆分为独立列 #

如果确定每个客户所属的组数量固定（比如最多3个），可以用条件聚合或专用PIVOT语法实现：

通用条件聚合（所有数据库兼容） #

假设最多3个组，生成group_1、group_2、group_3列：

SELECT 
  c.cID,
  MAX(CASE WHEN rn = 1 THEN gd.Name END) AS group_1,
  MAX(CASE WHEN rn = 2 THEN gd.Name END) AS group_2,
  MAX(CASE WHEN rn = 3 THEN gd.Name END) AS group_3
FROM (
  SELECT 
    c.cID,
    gd.Name,
    ROW_NUMBER() OVER(PARTITION BY c.cID ORDER BY gd.Name) AS rn
  FROM Customer c
  LEFT JOIN Groups g ON c.cID = g.cID
  LEFT JOIN GroupDefinitions gd ON gd.gID = g.gID
) t
GROUP BY c.cID;

SQL Server 专用PIVOT #

SELECT cID, [VIPs], [Region 1], [Region 2] -- 替换为实际存在的组名
FROM (
  SELECT 
    c.cID,
    gd.Name
  FROM Customer c
  LEFT JOIN Groups g ON c.cID = g.cID
  LEFT JOIN GroupDefinitions gd ON gd.gID = g.gID
) t
PIVOT (
  MAX(Name)
  FOR Name IN ([VIPs], [Region 1], [Region 2]) -- 匹配要转列的组名
) p;

3. 动态列数：组数量不固定时自动生成列 #

如果组的数量不确定，需要用动态SQL自动生成对应列：

MySQL 动态SQL示例 #

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN Name = ''',
      Name,
      ''' THEN Name END) AS ',
      CONCAT('`', Name, '`')
    )
  ) INTO @sql
FROM GroupDefinitions;

SET @sql = CONCAT('SELECT c.cID, ', @sql, ' 
                  FROM Customer c
                  LEFT JOIN Groups g ON c.cID = g.cID
                  LEFT JOIN GroupDefinitions gd ON gd.gID = g.gID
                  GROUP BY c.cID');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQL Server 动态SQL示例 #

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(Name) 
                      FROM GroupDefinitions
                      GROUP BY Name
                      ORDER BY Name
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)'),1,1,'');

SET @query = 'SELECT cID, ' + @cols + ' 
              FROM (
                    SELECT c.cID, gd.Name
                    FROM Customer c
                    LEFT JOIN Groups g ON c.cID = g.cID
                    LEFT JOIN GroupDefinitions gd ON gd.gID = g.gID
                  ) x
              PIVOT (
                    MAX(Name)
                    FOR Name IN (' + @cols + ')
                  ) p ';

EXECUTE(@query);

内容的提问来源于stack exchange，提问作者AGx-07_162