How to use ISNULL or COALESCE function when using a PIVOT in MS SQL

Question

I have a table with the below mentioned schema:

InvoiceID int PK,
Date datetime,
Amount Money

I now create a table which contains the Year, Month and Amount from the table above.

Year | Month | Amount 
2014 | Dec   | 10

I then pivot this table with all the months

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

The problem I have is that I need to set the months which don't have any amounts to zero instead of null

What RDBMS? Most have a `NVL` function for converting NULLs to a given value. — Mr. Llama, Dec 09 '14 at 17:34
@Jt2ouan: I did try that but it did not work. I guess it's because I am using pivot too, and each month is being hard coded. — Sid, Dec 09 '14 at 17:39
You will need to join to a tally table for this to work. The reason you are getting NULL is because there is no row for the months that have no data. — Sean Lange, Dec 09 '14 at 17:40
Just like what @SeanLange said you need to left join to like a months table so that the values aren't hardcoded. — Jt2ouan, Dec 09 '14 at 17:41
@SeanLange : I don't need to join any table as all the data i require is present in the single table itself. — Sid, Dec 09 '14 at 17:42

score 1 · Accepted Answer · answered Dec 09 '14 at 18:36

When you have a month without an invoice it's always going to generate a null value for the amount in that month when you pivot the table.

If you don't want to set up a tally table suggested in comments, you can go with something like this - but it's worth nothing that tally tables usually have better performance.

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

How to use ISNULL or COALESCE function when using a PIVOT in MS SQL

1 Answers1