MS SQL PIVOT统计年月季度数据时相同月份值导致季度求和错误

连续多次PIVOT时，每次PIVOT都会将当前数据集中所有未参与本次聚合、旋转的字段作为默认分组键。你的子查询是按UNITDATE分组生成数据，第一次按月份PIVOT后，剩余字段为quarter、year、各月份的聚合结果。其中2020-04的记录和2020-05的记录quarter都是Q2、year都是2020，进行第二次按季度PIVOT时，这两条记录的分组键完全一致，会被合并为同一行处理，导致其中一个月的数值被覆盖丢失，最终Q2求和结果错误。

直接改用条件聚合写法即可，逻辑更清晰，也能完全避免多次PIVOT带来的意外行合并问题，正确代码如下：

SELECT
    SUM(CASE WHEN DATEPART(MONTH, UNITDATE) = 1 THEN UNIT ELSE 0 END) AS Jan,
    SUM(CASE WHEN DATEPART(MONTH, UNITDATE) = 2 THEN UNIT ELSE 0 END) AS Feb,
    SUM(CASE WHEN DATEPART(MONTH, UNITDATE) = 3 THEN UNIT ELSE 0 END) AS Mar,
    SUM(CASE WHEN DATEPART(QUARTER, UNITDATE) = 1 THEN UNIT ELSE 0 END) AS Q1,
    SUM(CASE WHEN DATEPART(MONTH, UNITDATE) = 4 THEN UNIT ELSE 0 END) AS Apr,
    SUM(CASE WHEN DATEPART(MONTH, UNITDATE) = 5 THEN UNIT ELSE 0 END) AS May,
    SUM(CASE WHEN DATEPART(MONTH, UNITDATE) = 6 THEN UNIT ELSE 0 END) AS Jun,
    SUM(CASE WHEN DATEPART(QUARTER, UNITDATE) = 2 THEN UNIT ELSE 0 END) AS Q2,
    SUM(CASE WHEN DATEPART(MONTH, UNITDATE) = 7 THEN UNIT ELSE 0 END) AS Jul,
    SUM(CASE WHEN DATEPART(MONTH, UNITDATE) = 8 THEN UNIT ELSE 0 END) AS Aug,
    SUM(CASE WHEN DATEPART(MONTH, UNITDATE) = 9 THEN UNIT ELSE 0 END) AS Sep,
    SUM(CASE WHEN DATEPART(QUARTER, UNITDATE) = 3 THEN UNIT ELSE 0 END) AS Q3,
    SUM(CASE WHEN DATEPART(MONTH, UNITDATE) = 10 THEN UNIT ELSE 0 END) AS Oct,
    SUM(CASE WHEN DATEPART(MONTH, UNITDATE) = 11 THEN UNIT ELSE 0 END) AS Nov,
    SUM(CASE WHEN DATEPART(MONTH, UNITDATE) = 12 THEN UNIT ELSE 0 END) AS Dec,
    SUM(CASE WHEN DATEPART(QUARTER, UNITDATE) = 4 THEN UNIT ELSE 0 END) AS Q4,
    SUM(UNIT) AS YEARLY
FROM MyTable
WHERE DATEPART(YEAR, UNITDATE) = 2020

运行后可以得到正确的Q2结果1755，所有统计值都符合实际求和逻辑。如果必须保留PIVOT写法，需要在子查询中保留唯一行标识（比如原始的UNITDATE字段），每次PIVOT都保留该标识，避免不同行被错误合并，不过条件聚合写法可维护性更高，更推荐使用。

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