SQL分组数据透视需求：按部门、职位汇总季度ITEM数据

通用跨数据库解法（CASE WHEN + GROUP BY） #

这是适配绝大多数SQL数据库的方案，通过CASE WHEN将季度转换为列，配合聚合函数和分组实现透视效果：

SELECT
    DEPARMENT,
    JOB,
    SUM(CASE WHEN QUARTER = 'Q1' THEN ITEM ELSE 0 END) AS Q1_ITEM,
    SUM(CASE WHEN QUARTER = 'Q2' THEN ITEM ELSE 0 END) AS Q2_ITEM,
    SUM(CASE WHEN QUARTER = 'Q3' THEN ITEM ELSE 0 END) AS Q3_ITEM,
    SUM(CASE WHEN QUARTER = 'Q4' THEN ITEM ELSE 0 END) AS Q4_ITEM
FROM TESTHIRED_EMPLOYEE
GROUP BY DEPARMENT, JOB
ORDER BY DEPARMENT, JOB;

注意事项 #

• 如果需要统计ITEM的数量而非求和，将SUM替换为COUNT，并去掉ELSE 0（COUNT仅统计非NULL值）：

  SELECT
      DEPARMENT,
      JOB,
      COUNT(CASE WHEN QUARTER = 'Q1' THEN ITEM END) AS Q1_COUNT,
      COUNT(CASE WHEN QUARTER = 'Q2' THEN ITEM END) AS Q2_COUNT,
      COUNT(CASE WHEN QUARTER = 'Q3' THEN ITEM END) AS Q3_COUNT,
      COUNT(CASE WHEN QUARTER = 'Q4' THEN ITEM END) AS Q4_COUNT
  FROM TESTHIRED_EMPLOYEE
  GROUP BY DEPARMENT, JOB
  ORDER BY DEPARMENT, JOB;
  

• 若ITEM是字符串类型，可根据需求改用MAX/MIN等聚合函数。

SQL Server专用解法（PIVOT函数） #

使用SQL Server内置的PIVOT关键字简化透视逻辑：

SELECT DEPARMENT, JOB, Q1, Q2, Q3, Q4
FROM (
    SELECT DEPARMENT, JOB, QUARTER, ITEM
    FROM TESTHIRED_EMPLOYEE
) AS SourceTable
PIVOT (
    SUM(ITEM) -- 替换为需要的聚合函数：SUM/COUNT/AVG等
    FOR QUARTER IN (Q1, Q2, Q3, Q4)
) AS PivotTable
ORDER BY DEPARMENT, JOB;

Oracle专用解法（PIVOT函数） #

Oracle的PIVOT语法需为季度值添加引号：

SELECT DEPARMENT, JOB, Q1, Q2, Q3, Q4
FROM TESTHIRED_EMPLOYEE
PIVOT (
    SUM(ITEM) -- 替换为需要的聚合函数
    FOR QUARTER IN ('Q1' AS Q1, 'Q2' AS Q2, 'Q3' AS Q3, 'Q4' AS Q4)
)
ORDER BY DEPARMENT, JOB;

原GROUP BY未达预期的原因 #

如果之前仅按DEPARMENT, JOB, QUARTER分组，得到的是每行对应一个部门+职位+季度的记录，而非将季度转为列的透视表结构。上述方案通过列转行逻辑，将分散的季度数据聚合到同一行的不同列中，满足需求。

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