SQL Server 2012透视查询结果中如何将NULL替换为0？

架构老林

2026-5-15

解决SQL Server PIVOT后NULL替换为0的问题

嗨，我明白你的问题了——PIVOT操作后结果里的NULL值怎么换成0对吧？你之前尝试在SELECT里用ISNULL([Mth],0)没生效，是因为这些NULL并不是来自Mth字段，而是PIVOT转置后，某个Year+Market组合在对应月份没有匹配数据，聚合后生成的空值。下面给你直接有效的解决方法：

最直接的解决方案：外层SELECT处理每个月份列

因为PIVOT生成的月份列里的NULL是聚合后的结果，所以我们只需要在最终的SELECT语句中，对每个月份列用ISNULL()或者COALESCE()包裹，就能把NULL替换成0了。

完整的SQL代码如下：

SELECT 
    [Year],
    [Market],
    ISNULL([Jan], 0) AS [Jan],
    ISNULL([Feb], 0) AS [Feb],
    ISNULL([Mar], 0) AS [Mar],
    ISNULL([Apr], 0) AS [Apr],
    ISNULL([May], 0) AS [May],
    ISNULL([Jun], 0) AS [Jun],
    ISNULL([Jul], 0) AS [Jul],
    ISNULL([Aug], 0) AS [Aug],
    ISNULL([Sep], 0) AS [Sep],
    ISNULL([Oct], 0) AS [Oct],
    ISNULL([Nov], 0) AS [Nov],
    ISNULL([Dec], 0) AS [Dec]
FROM (
    SELECT [Mth], [Year], [Amount], [Market] FROM CTE1 
    UNION ALL 
    SELECT [Mth], [Year], [Amount], [Market] FROM CTE2
) x 
PIVOT (
    SUM([Amount]) FOR [Mth] IN ([Jan], [Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec])
) AS PVTTable;

执行后，你提供的示例结果里的NULL会全部变成0，输出会变成：