如何按条件将行数据求和值转换为列？SQL行转列问题

你的问题出在CASE表达式位置错误以及分组粒度太细，导致结果不符合预期。下面是两种常见场景的正确写法：

场景1：按员工（guid）分组，每个员工一行展示各月A状态计数 #

SELECT
    e.guid,
    SUM(CASE WHEN f.month_period = 'January' AND f.year_period = 2023 AND e.status='A' THEN 1 ELSE 0 END) AS 'January 2023 Count',
    SUM(CASE WHEN f.month_period = 'February' AND f.year_period = 2023 AND e.status='A' THEN 1 ELSE 0 END) AS 'February 2023 Count',
    SUM(CASE WHEN f.month_period = 'March' AND f.year_period = 2023 AND e.status='A' THEN 1 ELSE 0 END) AS 'March 2023 Count'
    -- 按需继续添加其他月份的CASE语句
FROM employees e
JOIN file_load f ON f.guid = e.guid
GROUP BY e.guid

场景2：全局汇总，一行展示各月A状态总计数 #

SELECT
    SUM(CASE WHEN f.month_period = 'January' AND f.year_period = 2023 AND e.status='A' THEN 1 ELSE 0 END) AS 'January 2023 Count',
    SUM(CASE WHEN f.month_period = 'February' AND f.year_period = 2023 AND e.status='A' THEN 1 ELSE 0 END) AS 'February 2023 Count',
    SUM(CASE WHEN f.month_period = 'March' AND f.year_period = 2023 AND e.status='A' THEN 1 ELSE 0 END) AS 'March 2023 Count'
    -- 按需继续添加其他月份的CASE语句
FROM employees e
JOIN file_load f ON f.guid = e.guid

错误原因说明 #

1. 原写法CASE在SUM外层：只有当前分组的month_period和year_period匹配时才会计算SUM，其余分组直接返回NULL，导致每行仅一列有值。
2. 分组粒度过细：原SQL按e.guid, f.month_period, f.year_period分组，每个员工的每个月都会生成一行数据，自然出现大量NULL值。

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