在动态透视SQL中将null替换为0

Question

我有一个动态枢轴sql是工作，因为我想，除了我不能找出在哪里把isnull在它从结果中删除空值。现在我输出的列中有一些空值我需要它们为零

下面是SQL和任何帮助将非常感激!

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(MonthYear) 
from temp
group by MonthYear, [Year], [Month]
order by [Year], [Month]
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,'')
set @query = 'SELECT Receive_Br, ' + @cols + ' from 
(
select Receive_Br, MonthYear, COGS 
from temp
) x
pivot 
(
max(COGS)
for MonthYear in (' + @cols + N')
) p '
execute(@query)

Answer 1

我建议在输出Null值的列的select语句中使用CASE语句。

的例子:

Case WHEN Column_Name IS NULL THEN 0 ELSE Column_Name END AS Column_Name

希望有帮助!

Answer 2

声明另一个变量来保存具有ISNULL函数的列，如下所示:

select @colsWithIsNull = STUFF((SELECT ', ISNULL(' + QUOTENAME(MonthYear) + ', 0) As ' + QUOTENAME(MonthYear)
from temp
group by MonthYear, [Year], [Month]
order by [Year], [Month]
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,'')

现在在您的查询中使用该变量，如下所示:

set @query = 'SELECT Receive_Br, ' + @colsWithIsNull + ' from 
(
select Receive_Br, MonthYear, COGS 
from temp
) x
pivot 
(
max(COGS)
for MonthYear in (' + @cols + N')
) p '
execute(@query)

从这里查看演示。