SQL Server动态Pivot如何动态将查询结果NULL值替换为0

之前修改内层fieldName的CASE逻辑不生效，核心原因是PIVOT返回的NULL不是源表字段的空值：这些NULL是行转列过程中，当前行（按CallReportNum维度聚合）不存在对应透视列的匹配记录时，PIVOT的MAX聚合函数自动生成的空值，根本不会走到内层查询的CASE判断分支里，所以在内层改字段逻辑完全碰不到这些NULL。

因为透视列是动态生成的，不需要手写每一列的ISNULL逻辑，单独生成一份给外层SELECT用的、包装了ISNULL的列清单即可，不需要改动原有给PIVOT用的列变量：

1. 新增变量@select_columns，专门存储外层查询要输出的、替换过NULL的列定义
2. 生成透视列名时，同时给两个变量赋值：@columns存PIVOT需要的原始列名，@select_columns存每个列套上ISNULL(列名, '0')的查询定义
3. 把动态SQL最外层的SELECT *替换为选择非透视维度列 + @select_columns即可

修改后的完整代码如下：

DECLARE 
    @columns NVARCHAR(MAX) = '', 
    @select_columns NVARCHAR(MAX) = '',
    @sql     NVARCHAR(MAX) = '';
    
-- 同时生成PIVOT用原始列清单、外层SELECT用带NULL替换的列清单
SELECT 
    @columns += QUOTENAME(s.fullColName) + ',',
    @select_columns += 'ISNULL(' + QUOTENAME(s.fullColName) + ', ''0'') AS ' + QUOTENAME(s.fullColName) + ','
FROM (SELECT  Case      
                WHEN g.MultiSelection = 0 THEN CONCAT(LEFT(c.[Name],62), ' - ', LEFT(g.[Name],62))
                WHEN g.MultiSelection = 1 THEN CONCAT(LEFT(c.[Name],40), ' - ', LEFT(g.[Name],40), ' - ', LEFT(f.[Name],40))
              END AS fullColName
             ,g.ReportVersionNum, g.[Name] AS gName ,c.[Name] AS cName
FROM
    (   
    SELECT ReportItemCategoryNum, ReportItemGroupNum, ReportVersionNum, [Name], MultiSelection
    FROM iCarolData.dbo.treportitemgroups
    WHERE ReportVersionNum = 60000
    ) g
    /* Join categories */
    LEFT JOIN
    (SELECT ReportItemCategoryNum, Name
      FROM iCarolData.dbo.treportItemCategories
      WHERE ReportVersionNum = 60000) c
    ON g.ReportItemCategoryNum = c.ReportItemCategoryNum
    /* Join Fields */
    Left Join
    (SELECT ReportItemFieldNum,ReportItemGroupNum,ReportItemCategoryNum, Name
      FROM iCarolData.dbo.treportitemfields
      WHERE ReportVersionNum = 60000) f
    ON g.ReportItemGroupNum = f.ReportItemGroupNum) s
WHERE gName != '...'
    AND cName != '...'
GROUP BY s.fullColName 

 /* 移除两个列清单末尾的多余逗号 */
SET @columns = LEFT(@columns, LEN(@columns) - 1);
SET @select_columns = LEFT(@select_columns, LEN(@select_columns) - 1);

/* 拼接动态SQL */
SET @sql ='
SELECT 
    CallReportNum,
    ChildOfCallReportNum,
    '+ @select_columns +'
FROM   
(
    SELECT top (100) PERCENT
         Case       
            WHEN groups.MultiSelection = 0 THEN CONCAT(LEFT(cat.[Name],62), '' - '', LEFT(groups.[Name],62))
            WHEN groups.MultiSelection = 1 THEN CONCAT(LEFT(cat.[Name],40), '' - '', LEFT(groups.[Name],40), '' - '', LEFT(fields.[Name],40)) 
        END AS fullColName
        ,repItems.CallReportNum
        ,CASE 
            WHEN repItems.ReportItemFieldNum != -1 AND groups.MultiSelection = 0 THEN fields.Name
            WHEN repItems.ReportItemFieldNum != -1 AND groups.MultiSelection = 1 AND fields.Name IS NOT NULL THEN ''1''
        END AS fieldName
        ,child.ChildOfCallReportNum
    FROM 
        (SELECT *
        FROM iCarolData.dbo.treportitems
        WHERE IsFinalized = 1) repItems
        /* Join default report data */
        LEFT JOIN iCarolData.dbo.treports defRep
        ON repItems.callReportNum = defRep.callreportNum
        /* Join Report Version map */
        LEFT JOIN iCarolData.dbo.treportsversion vers
        ON defRep.ReportVersionNum = vers.ReportVersionNum
        /* Join Categories */
        LEFT JOIN iCarolData.dbo.treportItemCategories cat
        ON repItems.ReportItemCategoryNum = cat.ReportItemCategoryNum
        /* Join Groups (questions) */
        LEFT JOIN iCarolData.dbo.treportitemgroups groups
        ON repItems.ReportItemGroupNum = groups.ReportItemGroupNum
        /* Join Fields (answers) */
        LEFT JOIN iCarolData.dbo.treportitemfields fields
        ON repitems.ReportItemFieldNum = fields.ReportItemFieldNum
        /* Join children report map */
        LEFT JOIN iCarolData.dbo.tReportsChildren child
        ON repItems.CallReportNum = child.CallReportNum
    WHERE
        groups.ReportVersionNum = 60000
    GROUP BY
         repItems.CallReportNum
         ,CONCAT(LEFT(cat.[Name],62), '' - '', LEFT(groups.[Name],62))
         ,CONCAT(LEFT(cat.[Name],40), '' - '', LEFT(groups.[Name],40), '' - '', LEFT(fields.[Name],40))
         ,fields.[Name],repItems.ReportItemFieldNum
        ,fields.Name
        ,child.ChildOfCallReportNum,groups.MultiSelection
) t 

PIVOT(
    MAX(fieldName) 
    FOR fullColName IN ('+ @columns +')
) AS pivot_table;';

-- 执行动态SQL
EXECUTE sp_executesql @sql;

• 不要修改给PIVOT的IN子句用的@columns变量，PIVOT的IN参数里只能放原始列名，不能加ISNULL这类函数
• 后续业务新增透视列时，@select_columns会自动同步生成对应的NULL替换逻辑，不需要手动修改代码
• 如果需要返回数值类型的0而非字符串'0'，把ISNULL(xxx, ''0'')改成ISNULL(xxx, 0)即可，注意和原有字段值（Yes/No/1）的类型兼容，避免隐式转换报错

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