问题描述
图像转录:
FTE/RATE CARD | 2018-01-01 | 2018-02-01 | 2018-03-01 | 2018-04-01 | 2018-05-01 | 2018-06-01 | ... ------------------------------------------------------------------------------------------------- FTE 3 | NULL | NULL | NULL | 33 | 38 | 40.5 | ...
我有一个数据透视 sql 查询,如下所示.我需要在结果中用零替换 NULL 值.我不知道在查询中我究竟在哪里使用 IsNull 或 Coalesce 函数.
I have a pivot sql query as below. I need to replace NULL values with Zeros in the result. I don't know where do I exactly use the IsNull or Coalesce function in the query.
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) SELECT @cols = STUFF ( ( SELECT distinct ',' + QUOTENAME([MONTH]) FROM [HEADCOUNT] WHERE [MONTH] BETWEEN '01-012018' AND '12-01-2018' ORDER BY ',' + QUOTENAME([MONTH]) FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'' ) SET @query = 'SELECT [FTE/RATE CARD],' + @cols + ' FROM ( SELECT [MONTH],[FTE/RATE CARD],[HC] FROM [HEADCOUNT] WHERE [CC-LOC] IN ([CC-LOC]) ) x pivot (Sum ([HC]) for [MONTH] in (' + @cols + '))p' execute(@query)
推荐答案
您需要为 select 语句生成另一个列列表,并用 ISNULL 包裹各个月份,如下所示:
You'll need to generate another column list for the select statement, and wrap individual months with ISNULL, like so:
DECLARE @cols AS NVARCHAR(MAX), @selectCols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) SELECT @selectCols = STUFF ( ( SELECT distinct ', ISNULL(' + QUOTENAME([MONTH]) + ', 0) AS ' + QUOTENAME([MONTH]) FROM [HEADCOUNT] WHERE [MONTH] BETWEEN '01-012018' AND '12-01-2018' ORDER BY ',' + QUOTENAME([MONTH]) FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'' ) SELECT @cols = STUFF ( ( SELECT distinct ',' + QUOTENAME([MONTH]) FROM [HEADCOUNT] WHERE [MONTH] BETWEEN '01-012018' AND '12-01-2018' ORDER BY ',' + QUOTENAME([MONTH]) FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'' ) SET @query = 'SELECT [FTE/RATE CARD],' + @selectCols + ' FROM ( SELECT [MONTH],[FTE/RATE CARD],[HC] FROM [HEADCOUNT] WHERE [CC-LOC] IN ([CC-LOC]) ) x pivot (Sum ([HC]) for [MONTH] in (' + @cols + '))p' execute(@query)