动态PIVOT查询中如何处理NULL值实现GROUP BY分组聚合

PIVOT 运算符默认将源数据中除聚合字段、待行转列字段之外的所有字段作为分组依据。你的原查询中，PIVOT的源子查询返回了ControlNo、Line、Code、Profit四个字段，因此Code也会被纳入分组规则：同一个ControlNo下只要Code值不同（包括NULL和非空的差异），就会被拆分为多条记录，这就是重复行出现的核心原因。

目标是同一个ControlNo仅返回一行，合并Code的非空值、Line列的求和结果，可参考如下调整：

步骤1：调整动态列生成逻辑，自动处理NULL为0 #

如果需要将结果中的NULL值显示为0，先修改@columns的生成逻辑，为每个转置后的列套上ISNULL判断：

SET @columns = STUFF((SELECT distinct ', ISNULL(' + QUOTENAME(Line) + ',0) AS ' + QUOTENAME(Line) 
FROM #Table1 FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'')

步骤2：调整PIVOT源数据，统一同ControlNo的Code值 #

在PIVOT之前，先通过窗口函数给同一个ControlNo的所有行补全统一的Code（取非空值即可，这里用MAX聚合），避免Code导致分组拆分：

set @finalquery = '
        select  p.ControlNo,
                p.Code,' + @columns + '
        from ( 
            select 
                ControlNo, 
                Line, 
                Profit,
                -- 同ControlNo共用同一个Code（取非空值）
                MAX(Code) OVER(PARTITION BY ControlNo) AS Code
            from #Table1
        )a 
    pivot 
    ( 
     SUM(Profit) 
     for Line IN (' + REPLACE(REPLACE(@columns,'ISNULL(',''),',0) AS ','') + ') 
    )p 
    GROUP BY p.ControlNo,p.Code
    ORDER BY ControlNo
    '
exec(@finalquery)

备注：如果不想用REPLACE处理列名，也可以单独声明一个@columnsForPivot变量存储纯列名用于PIVOT的IN子句，代码可读性会更好。

最终输出效果 #

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