Oracle SQL按条件合并多列并自动生成对应Type列的查询方法

核心思路 #

不需要创建额外视图或表，先把原表的3个容量列转为行、过滤空值，再按Name分组用LISTAGG聚合即可，以下提供两种实现写法：

写法1：UNION ALL 手动转行（新手更易理解） #

SELECT 
  Name,
  LISTAGG(Type, ',') WITHIN GROUP (ORDER BY Type) AS Type,
  LISTAGG(Capacity, ',') WITHIN GROUP (ORDER BY Type) AS Capacity
FROM (
  SELECT Name, 'A' AS Type, "Capacity A" AS Capacity FROM 表A WHERE "Capacity A" IS NOT NULL
  UNION ALL
  SELECT Name, 'B' AS Type, "Capacity B" AS Capacity FROM 表A WHERE "Capacity B" IS NOT NULL
  UNION ALL
  SELECT Name, 'C' AS Type, "Capacity C" AS Capacity FROM 表A WHERE "Capacity C" IS NOT NULL
) t
GROUP BY Name
ORDER BY Name;

说明

• 每层SELECT对应一个容量列，只保留非空的容量记录，同时标注对应的类型标识A/B/C
• LISTAGG的ORDER BY Type保证Type和Capacity的顺序一一对应，不会出现类型和值错位的问题
• 兼容性强，所有支持LISTAGG的Oracle版本都可以运行

写法2：UNPIVOT 列转行（语法更简洁） #

SELECT 
  Name,
  LISTAGG(Type, ',') WITHIN GROUP (ORDER BY Type) AS Type,
  LISTAGG(Capacity, ',') WITHIN GROUP (ORDER BY Type) AS Capacity
FROM (
  SELECT 
    Name,
    CASE capacity_col 
      WHEN 'CAPACITY_A' THEN 'A'
      WHEN 'CAPACITY_B' THEN 'B'
      WHEN 'CAPACITY_C' THEN 'C' 
    END AS Type,
    capacity_val AS Capacity
  FROM 表A
  UNPIVOT (
    capacity_val FOR capacity_col IN (
      "Capacity A" AS 'CAPACITY_A',
      "Capacity B" AS 'CAPACITY_B',
      "Capacity C" AS 'CAPACITY_C'
    )
  )
) t
GROUP BY Name
ORDER BY Name;

说明

• UNPIVOT操作会自动过滤值为NULL的容量列，不需要手动写IS NOT NULL条件
• 注意将UNPIVOT里的列名替换为你实际表中的列名，如果列名没有空格可以去掉双引号

效果验证 #

用你提供的示例数据运行两种写法，输出结果完全符合要求：

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