如何实现表格部分转置？SQL合并相同id的多列数据问询

原始数据 #

原始表格 #

说明：id和source构成唯一键，source的取值仅为'a'、'b'或'c'。

目标结果 #

目标表格 #

注：允许用null替代0。

已尝试的操作及问题 #

尝试的SQL语句 #

select id, score as score_a, null as score_b, null as score_c from tbl where source = 'a'
union all
select id, null as score_a, score as score_b, null as score_c from tbl where source = 'b'
union all
select id, null as score_a, null as score_b, score as score_c from tbl where source = 'c'

当前得到的错误结果 #

问题：同一个id对应多行数据，需要合并为每行唯一id的宽表。

解决方案 #

方法1：基于现有查询加聚合分组 #

在你现有查询的基础上，通过GROUP BY id聚合，用MAX()提取每个id对应的非空值，再用COALESCE()把null替换为0：

SELECT
    id,
    COALESCE(MAX(score_a), 0) AS score_a,
    COALESCE(MAX(score_b), 0) AS score_b,
    COALESCE(MAX(score_c), 0) AS score_c
FROM (
    select id, score as score_a, null as score_b, null as score_c from tbl where source = 'a'
    union all
    select id, null as score_a, score as score_b, null as score_c from tbl where source = 'b'
    union all
    select id, null as score_a, null as score_b, score as score_c from tbl where source = 'c'
) AS temp
GROUP BY id;

如果接受null，可以去掉COALESCE()，直接用MAX(score_a)即可。

方法2：CASE WHEN直接转换（更简洁） #

这是行转列的标准写法，无需子查询，直接按id分组，用CASE判断source取值匹配对应列：

SELECT
    id,
    COALESCE(MAX(CASE WHEN source = 'a' THEN score END), 0) AS score_a,
    COALESCE(MAX(CASE WHEN source = 'b' THEN score END), 0) AS score_b,
    COALESCE(MAX(CASE WHEN source = 'c' THEN score END), 0) AS score_c
FROM tbl
GROUP BY id;

若允许null，去掉COALESCE()即可：

SELECT
    id,
    MAX(CASE WHEN source = 'a' THEN score END) AS score_a,
    MAX(CASE WHEN source = 'b' THEN score END) AS score_b,
    MAX(CASE WHEN source = 'c' THEN score END) AS score_c
FROM tbl
GROUP BY id;

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