Remove NULL values from SQL PIVOT

Transact-SQL
1

I have this

SELECT * FROM 
(
SELECT 
[cmpcode],
[el2],
[period],
[curdoc],
[valuedoc]
FROM [icarus].[dbo].[fxm_dochead_allyears_full]
WHERE [yr] = '2021' AND cmpcode = 'Z992'
) x

PIVOT
(
SUM ([valuedoc])
FOR [period] IN
([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
)

AS pivot_table;

This works but generates lots of NULL values where there are no values for a particular month.

Where do I wrap my ISNULL? Everywhere I've tried doesn't work and if I try

ISNULL ([1], 0) AS [1]

inside of the PIVOT piece of code it errors without running.

Any help appreciated and thank you for reading.

2

you have to do it where Select * is

SELECT [cmpcode],
[el2],
[period],
[curdoc],
IsNull([1],0) as [1],
IsNull([2],0) as [2],
IsNull([3],0) as [3],
IsNull([4],0) as [4],
IsNull([5],0) as [5],
IsNull([6],0) as [6],
IsNull([7],0) as [7],
IsNull([8],0) as [8],
IsNull([9],0) as [9],
IsNull([10],0) as [10],
IsNull([11],0) as [11],
IsNull([12],0) as [12]
FROM
(
SELECT
[cmpcode],
[el2],
[period],
[curdoc],
[valuedoc]
FROM [icarus].[dbo].[fxm_dochead_allyears_full]
WHERE [yr] = '2021' AND cmpcode = 'Z992'
) x

PIVOT
(
SUM ([valuedoc])
FOR [period] IN
([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
)

AS pivot_table;

1 Like
3

Genius, many thanks!

4

I could be wrong but I'm thinking that all the code posted, so far, is missing a GROUP BY. And, if we're going to do the ISNULL thing, let's make things even more useful and make it easier to do line totals, etc.

Like I said, I could be wrong in what you're trying to do but try the following with the understanding that I don't have your data to test it on.

 SELECT  [cmpcode]
        ,[el2]
        ,[curdoc]
        ,[Jan]    = SUM(IIF([period] =  1,valuedoc,0)
        ,[Feb]    = SUM(IIF([period] =  2,valuedoc,0)
        ,[Mar]    = SUM(IIF([period] =  3,valuedoc,0)
        ,[Apr]    = SUM(IIF([period] =  4,valuedoc,0)
        ,[May]    = SUM(IIF([period] =  5,valuedoc,0)
        ,[Jun]    = SUM(IIF([period] =  6,valuedoc,0)
        ,[Jul]    = SUM(IIF([period] =  7,valuedoc,0)
        ,[Aug]    = SUM(IIF([period] =  8,valuedoc,0)
        ,[Sep]    = SUM(IIF([period] =  9,valuedoc,0)
        ,[Oct]    = SUM(IIF([period] = 10,valuedoc,0)
        ,[Nov]    = SUM(IIF([period] = 11,valuedoc,0)
        ,[Dec]    = SUM(IIF([period] = 12,valuedoc,0)
        ,[Total]  = SUM(valuedoc)
   FROM icarus.dbo.fxm_dochead_allyears_full
  GROUP BY [cmpcode],[el2],[curdoc]
  WHERE yr      = '2021' 
    AND cmpcode = 'Z992'
;