如何在MS SQL中使用PIVOT时使用ISNULL或COALESCE函数

Question

我有一个表，其中包含下面提到的模式：

InvoiceID int PK,
Date datetime,
Amount Money

我现在创建一个表格，其中包含上表中的年、月和金额。

Year | Month | Amount 
2014 | Dec   | 10

然后，我将所有月份都透视此表

select * from (select year(Date) as InvoiceYear, isnull(left(datename(month, date),3),0) as InvoiceMonth, Amount from Invoice) as Sid pivot ( sum(Amount) for InvoiceMonth in (jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec)) as P1

我遇到的问题是我需要将没有任何金额的月份设置为零而不是 null

Answer 1

当您有一个月没有发票时，当您透视表时，它总是会为该月的金额生成一个空值。

如果您不想设置评论中建议的计数表，则可以使用这样的东西 - 但是计数表通常具有更好的性能毫无价值。

SELECT 
 invoiceYear,
 isnull(jan,0) as [jan],
 isnull(feb,0) as [feb],
 etc...
 FROM
 (select * from 
(select year(Date) as InvoiceYear, 
isnull(left(datename(month, date),3),0) as InvoiceMonth,
 Amount from Invoice) 
 as Sid 
 pivot ( sum(Amount) for InvoiceMonth in (jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec)) as P1
 ) A
 GROUP BY invoiceYear