How to replace NULL with zero in the output of this pivot query?

Question

I am using SQL Server 2012 and I have the following SQL query:

SELECT * 
FROM 
    (SELECT [Mth], [Year], [Amount], [Market] 
     FROM CTE1

     UNION ALL

     SELECT [Mth], [Year], [Amount], [Market] 
     FROM CTE2) x
PIVOT
    (SUM(x.[Amount) 
     FOR x.[Mth] IN ([Jan], [Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec])
    ) AS PVTTable

Extract of output is as follows:

Year    Market   Jan Feb  Mar   Apr  May  Jun   Jul     Aug    Sep      Oct    Nov   Dec
2017    France   11  20   NULL  16   NULL  8    NULL    NULL    NULL    NULL    5     9

I want to replace the NULL with zero. I have tried ISNULL([Mth],0) in my SELECT queries but it's not working.

You need to do it in the outermost select rather than select *, e.g. select isnull([Jan], 0) — dbajtr, Jan 30 '18 at 11:28
@GordonLinoff You are right. It should be a UNION ALL and also, the second SELECT is from CTE2. Updated it my question. — user3115933, Jan 30 '18 at 12:28
Bohemian - yes this is a duplicate. But the accepted answer for the question you refer to doesn't work. As pointed out there, any cells generated in the pivot which contain nulls will not be catered for by that answer. — steve, Mar 03 '19 at 11:40

score 0 · Accepted Answer · answered Jan 30 '18 at 11:28

It's SUM(x.[Amount]) that has the value NULL here, not [mth]. I'm not sure you can, however, use an ISNULL in the PIVOT (no Sample data with which to actually check (and I tend to use do a cross pivot, rather than using PIVOT). If you can't you'll need to put the ISNULL in your outer SELECT statemeent:

SELECT [Year], Market,
       ISNULL(Jan,0) AS Jan,
       ISNULL(Feb,0) AS Feb, ...

How to replace NULL with zero in the output of this pivot query?

1 Answers1