SQL查询：如何将同一casenum的多行程序数据合并为单行？

当前执行的SQL代码 #

CREATE TABLE cases AS
SELECT 
   casenum
FROM cases;

CREATE TABLE programs AS
SELECT
   casenum
   ,CASE
      WHEN prgm_cd='ra' THEN 'y'
      ELSE NULL
   END AS resea
   ,CASE
      WHEN prgm_cd='tf' THEN 'y'
      ELSE NULL
   END AS tanf
   ,CASE
      WHEN prgm_cd='ta' THEN 'y'
      ELSE NULL
   END AS taa
FROM prgms;

SELECT 
   casenum
   ,resea
   ,tanf
   ,taa
FROM cases
LEFT JOIN programs on cases.casenum = programs.casenum;

当前查询结果 #

需求说明 #

无论案件参与多少个程序，每个casenum仅显示一行数据，所有参与的程序列需标记为'y'，示例结果如下：

正确解决方案 #

问题核心在于原SQL只是把单条程序记录转成列，但没有对同一案件的多条程序记录做聚合。正确做法是用MAX()函数按案件分组提取每个程序的有效标记（MAX会忽略NULL，只要案件参与过对应程序就会保留'y'），再关联案件表。

简化版（无需临时表） #

SELECT 
    c.casenum,
    MAX(CASE WHEN p.prgm_cd='ra' THEN 'y' END) AS resea,
    MAX(CASE WHEN p.prgm_cd='tf' THEN 'y' END) AS tanf,
    MAX(CASE WHEN p.prgm_cd='ta' THEN 'y' END) AS taa
FROM cases c
LEFT JOIN prgms p ON c.casenum = p.casenum
GROUP BY c.casenum;

保留临时表的修改版 #

CREATE TABLE cases AS
SELECT casenum FROM cases;

CREATE TABLE programs AS
SELECT
    casenum,
    MAX(CASE WHEN prgm_cd='ra' THEN 'y' END) AS resea,
    MAX(CASE WHEN prgm_cd='tf' THEN 'y' END) AS tanf,
    MAX(CASE WHEN prgm_cd='ta' THEN 'y' END) AS taa
FROM prgms
GROUP BY casenum;

SELECT 
    c.casenum,
    p.resea,
    p.tanf,
    p.taa
FROM cases c
LEFT JOIN programs p ON c.casenum = p.casenum;

之前尝试无效的原因 #

• 移除CASE中的ELSE NULL：NULL是CASE表达式的默认返回值，移除后结果无变化，且原prgms表是每个程序一行，转换后还是多行，无法合并。
• 用rownum() OVER(PARTITION BY casenum...)取第一行：只会拿到该案件某一条程序的记录，其他程序列仍为NULL，无法整合所有程序标记到一行。

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