T-SQL使用PIVOT透视时如何对目标列求和去重及TerrPrem丢失排查

原因分析 #

SQL Server的PIVOT运算符会自动将所有未出现在聚合函数、FOR子句中的字段作为分组依据。你原查询的数据源包含ControlNo、Line、Profit、Fee四个字段，因此PIVOT会同时按ControlNo和Fee分组，同一个ControlNo下Fee不同就会产生多行，无法实现去重和Fee求和的需求。

解决代码 #

更推荐用灵活度更高的条件聚合实现，完全符合你的期望输出：

SELECT 
    ControlNo,
    SUM(Fee) AS Fee,
    ISNULL(SUM(CASE WHEN Line = 'Line1' THEN Profit END), 0) AS Line1,
    ISNULL(SUM(CASE WHEN Line = 'Line2' THEN Profit END), 0) AS Line2
FROM #Table1
GROUP BY ControlNo
ORDER BY ControlNo

如果一定要用PIVOT语法实现，需要先预处理数据源，排除不需要的分组维度，提前计算总Fee：

SELECT pvt.ControlNo, t.TotalFee, ISNULL(pvt.Line1,0) Line1, ISNULL(pvt.Line2,0) Line2
FROM (
    SELECT DISTINCT ControlNo, SUM(Fee) OVER(PARTITION BY ControlNo) TotalFee 
    FROM #Table1
) t
INNER JOIN (
    SELECT ControlNo, Line1, Line2
    FROM (SELECT ControlNo, Line, Profit FROM #Table1) s
    PIVOT(SUM(Profit) FOR Line IN ([Line1], [Line2])) p
) pvt ON t.ControlNo = pvt.ControlNo
ORDER BY pvt.ControlNo

原因说明 #

这不是异常，是PIVOT的正常运行逻辑。你第二次的表包含Guid、ControlNo、Line、Prem、TerrPrem五个字段，PIVOT时会自动把Guid、ControlNo、TerrPrem都作为分组键，你的测试数据中TerrPrem存在0和81两个不同值，自然就会被拆分为两行。

解决代码 #

如果需要按Guid、ControlNo分组聚合，同样用条件聚合实现即可：

SELECT 
    Guid,
    ControlNo,
    SUM(TerrPrem) AS TerrPrem,
    ISNULL(SUM(CASE WHEN Line = 'Commercial General Liability' THEN Prem END), 0) AS [Commercial General Liability],
    ISNULL(SUM(CASE WHEN Line = 'Contractors Pollution Liability' THEN Prem END), 0) AS [Contractors Pollution Liability]
FROM #Table1
GROUP BY Guid, ControlNo

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