Oracle SQL：将同表多行数据合并为单行

嘿，我来帮你搞定这个SQL查询需求！根据你的描述，要把每个identifier对应的多行数据合并成一行，同时展示insertTime以及每个step对应的Yes和No相关值，这里用条件聚合是最适合的方案——这也是处理这类行转列场景的常用手段。

方案1：统计每个Step下Yes/No的数量 #

如果你的需求是统计每个identifier在每个step下Yes和No的出现次数，用下面的SQL就可以：

SELECT
    identifier,
    MAX(insertTime) AS insertTime, -- 同一identifier的insertTime通常一致，用MAX/MIN都能拿到正确值
    -- 针对每个step生成对应的Yes/No统计列
    SUM(CASE WHEN step = '1' AND answer = 'Yes' THEN 1 ELSE 0 END) AS step1_Yes,
    SUM(CASE WHEN step = '1' AND answer = 'No' THEN 1 ELSE 0 END) AS step1_No,
    SUM(CASE WHEN step = '2' AND answer = 'Yes' THEN 1 ELSE 0 END) AS step2_Yes,
    SUM(CASE WHEN step = '2' AND answer = 'No' THEN 1 ELSE 0 END) AS step2_No,
    SUM(CASE WHEN step = '3' AND answer = 'Yes' THEN 1 ELSE 0 END) AS step3_Yes,
    SUM(CASE WHEN step = '3' AND answer = 'No' THEN 1 ELSE 0 END) AS step3_No,
    SUM(CASE WHEN step = '4' AND answer = 'Yes' THEN 1 ELSE 0 END) AS step4_Yes,
    SUM(CASE WHEN step = '4' AND answer = 'No' THEN 1 ELSE 0 END) AS step4_No,
    SUM(CASE WHEN step = '5' AND answer = 'Yes' THEN 1 ELSE 0 END) AS step5_Yes,
    SUM(CASE WHEN step = '5' AND answer = 'No' THEN 1 ELSE 0 END) AS step5_No,
    SUM(CASE WHEN step = '6' AND answer = 'Yes' THEN 1 ELSE 0 END) AS step6_Yes,
    SUM(CASE WHEN step = '6' AND answer = 'No' THEN 1 ELSE 0 END) AS step6_No
FROM
    your_table -- 替换成你的实际表名
GROUP BY
    identifier
ORDER BY
    identifier;

方案2：展示每个Step对应的Yes/No状态（单值） #

如果每个identifier+step组合只有一行数据（也就是每个step下只有Yes或No其中一个），你可以用下面的SQL直接展示对应的状态，没有的会显示NULL：

SELECT
    identifier,
    MAX(insertTime) AS insertTime,
    MAX(CASE WHEN step = '1' AND answer = 'Yes' THEN answer ELSE NULL END) AS step1_Yes,
    MAX(CASE WHEN step = '1' AND answer = 'No' THEN answer ELSE NULL END) AS step1_No,
    MAX(CASE WHEN step = '2' AND answer = 'Yes' THEN answer ELSE NULL END) AS step2_Yes,
    MAX(CASE WHEN step = '2' AND answer = 'No' THEN answer ELSE NULL END) AS step2_No,
    -- 重复上述格式直到step6
    MAX(CASE WHEN step = '6' AND answer = 'Yes' THEN answer ELSE NULL END) AS step6_Yes,
    MAX(CASE WHEN step = '6' AND answer = 'No' THEN answer ELSE NULL END) AS step6_No
FROM
    your_table -- 替换成你的实际表名
GROUP BY
    identifier
ORDER BY
    identifier;

小提示 #

• 如果你的step不是数字（比如是'step_a'这类字符串），只要把CASE语句里的step = '1'改成对应的字符串值就行。
• 如果你的step数量不固定、是动态的，那可能需要用动态SQL来自动生成列，但静态SQL在step固定时效率更高、更易维护。

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