Replace nulls with 0 in Dynamic Pivot SQL-CodePudding

I have a dynamic pivot sql that is working as I'd like except I can't figure out where to put the isnull in it to remove the null values from the results. Right now I have some null values in the columns that are outputted that I need to be zero.

Below is the SQL and any help would be greatly appreciated!

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ','   QUOTENAME(MonthYear) 
                    from temp
                    group by MonthYear, [Year], [Month]
                    order by [Year], [Month]
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT Receive_Br, '   @cols   ' from 
             (
                select Receive_Br, MonthYear, COGS 
                from temp
            ) x
            pivot 
            (
                max(COGS)
                for MonthYear in ('   @cols   N')
            ) p '

execute(@query)

CodePudding user response：

I would suggest using CASE statement in the select statement on the column that is outputting Null values.

Example:

Case WHEN Column_Name IS NULL THEN 0 ELSE Column_Name END AS Column_Name

Hope that helps!

CodePudding user response：

Declare another variable to hold the columns with ISNULL function as the following:

select @colsWithIsNull = STUFF((SELECT ', ISNULL('   QUOTENAME(MonthYear)   ', 0) As '   QUOTENAME(MonthYear)
                    from temp
                    group by MonthYear, [Year], [Month]
                    order by [Year], [Month]
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

Now use that variable with your query as the following:

set @query = 'SELECT Receive_Br, '   @colsWithIsNull   ' from 
             (
                select Receive_Br, MonthYear, COGS 
                from temp
            ) x
            pivot 
            (
                max(COGS)
                for MonthYear in ('   @cols   N')
            ) p '

execute(@query)

See a demo from here.