在SQL Server 2008中的PIVOT查询中使用ISNULL

Question

我正在使用SQL Pivot列出每个产品每天的销售额总额，但我在替换行的NULL值时遇到了问题。

下表是我的数据示例

Date    | Product |  Amount  | Price  |
------------+---------+----------+--------+
2018-01-06  |  PRO 1  |    2     |   5    |
2018-01-06  |  PRO 2  |    3     |   6    |
2018-01-06  |  PRO 2  |    1     |   6    |
2018-01-06  |  PRO 3  |    4     |   18   |
2018-01-06  |  PRO 4  |    2     |   5    |
2018-01-06  |  PRO 5  |    3     |   6    |
2018-01-06  |  PRO 6  |    2     |   3    |
2018-01-06  |  PRO 6  |    7     |   3    |
2018-01-07  |  PRO 6  |    7     |   3    |

我的查询：

DECLARE @cols  AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';
SELECT @cols = @cols + QUOTENAME([Product]) + ',' FROM (select distinct [Product] from #sales) as tmp
SELECT @cols = substring(@cols, 0, len(@cols)) --trim "," at end
set @query =
'SELECT * from (
Select [Date], ([Amount]*[Price]) as [TOTAL], [Product] from #Sales group by Date, Product, Amount, Price
) src
pivot
(
SUM([TOTAL]) for [Product] in (' + @cols + ')
) piv'
execute(@query)

我的结果：

Date    | PRO 1  | PRO 2  | PRO 3  | PRO 4  | PRO 5  | PRO 6  |
-----------+--------+--------+--------+--------+--------+--------|
2018-01-06 |   10   |   24   |   72   |   10   |   18   |   27   |
2018-01-07 |  NULL  |  NULL  |  NULL  |  NULL  |  NULL  |   21   |

如何将NULL值替换为0？做ISNULL(([Amount]*[Price]),0)似乎对它没有任何帮助。

Answer 1

必须用列的动态列表替换*，并在其中使用ISNULL。生成这个列表的代码可能是这样的：

DECLARE @cols2  AS NVARCHAR(MAX)='[Date], ';
SELECT @cols2 = @cols2 + 'ISNULL(' + QUOTENAME([Product]) + ', 0) as ' + QUOTENAME([Product]) + ',' FROM (select distinct [Product] from #sales) as tmp
SELECT @cols2 = substring(@cols2, 0, len(@cols2)) --trim "," at end