Revokez Revokez - 2 months ago 10
SQL Question

Returning "0" for NULL values within Dynamic Pivot for SQL Server

I have the following Code:

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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(month)
from PRCombinedRM
group by month,AccountNumber
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

set @query = 'SELECT AccountNumber,' + 'FullName,' + 'AccountType,' + 'Company,' + 'AccountBalance,' + @cols + ' from
(
select AccountNumber,
FullName,
AccountType,
Company,
AccountBalance,
month,
amount
from PRCombinedRM
) x
pivot
(
sum(amount)
for month in (' + @cols + ')
) p '

execute(@query)


However currently the results that this is outputting shows the values for "amount" as a NULL, however I would like to replace the NULL values with "0" instead.
How would I go about doing this?

Currently the data outputs as such:

AccountNumber FullName AccountType Company AccountBalance Aug Jul Jun Sep
100 M R Test Test Account Test Company 100 -50 -50 NULL -50


However I would like the data to output as:

AccountNumber FullName AccountType Company AccountBalance Aug Jul Jun Sep
100 M R Test Test Account Test Company 100 -50 -50 0 -50


Thank you.

Answer

I would use another variable to store the ISNULL(someColumn,0):

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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(month)                   
               from PRCombinedRM    
               group by month,AccountNumber 
        FOR XML PATH(''), TYPE      
        ).value('.', 'NVARCHAR(MAX)')       
    ,1,1,'');

SET @cols2 = STUFF((SELECT distinct ', ISNULL(' + QUOTENAME(month) + ',0) ' + QUOTENAME(month)
               from PRCombinedRM    
               group by month,AccountNumber 
        FOR XML PATH(''), TYPE      
        ).value('.', 'NVARCHAR(MAX)')       
    ,1,1,'');

set @query = 'SELECT AccountNumber,' + 'FullName,' + 'AccountType,' + 'Company,' + 'AccountBalance,' + @cols2 + ' from                   
         (      
            select AccountNumber,   
                   FullName,
                   AccountType,
                   Company,
                   AccountBalance,
                   month,
                   Amount
                from PRCombinedRM
            ) x 
            pivot   
            (   
                sum(amount)
                for month in (' + @cols + ')
            ) p ';

execute(@query);   
Comments