how to remove null values from an sql pivot?

How can i replace the null values with zero in the sql pivot table?

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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.[Offer_cover]) 
            FROM #cover2 c 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT Ref,role_name,offer_id, ' + @cols + ' from 
            (
                select*
                from #cover2 
           ) x
            pivot 
            (
                 SUM(cover_earning_Count)
                for [offer_cover] in (' + @cols + ') 
            ) p   '
execute(@query)

Output:

Ref         role_name    offer_id   10000   104000  112000
43132_43282 Call Center  1          1       NULL    NULL
43132_43282 Others       1          2       NULL    NULL
43160_43282 Call Center  1          6       NULL    1
43160_43282 Others       1          NULL    1       NULL
43191_43282 Call Center  1          7       NULL    NULL
43191_43282 Others       1          3       1       1
43221_43282 Call Center  1          4       1       1
43221_43282 Others       1          2       NULL    NULL

Cover2 Table

Ref YEAR    MONTH   Role_name   offer_cover offer_id    Cover_Earning_Count CONF_DATE   Curr_date
43132_43282 2018    2   Call Center 2000    1   5   2/1/2018    7/1/2018
43132_43282 2018    2   Call Center 4000    1   8   2/1/2018    7/1/2018
43132_43282 2018    2   Call Center 6000    1   2   2/1/2018    7/1/2018
43132_43282 2018    2   Call Center 8000    1   4   2/1/2018    7/1/2018
43132_43282 2018    2   Call Center 10000   1   1   2/1/2018    7/1/2018
43132_43282 2018    2   Call Center 12000   1   6   2/1/2018    7/1/2018
43132_43282 2018    2   Call Center 14000   1   2   2/1/2018    7/1/2018
43132_43282 2018    2   Call Center 16000   1   4   2/1/2018    7/1/2018
43132_43282 2018    2   Call Center 20000   1   6   2/1/2018    7/1/2018
43132_43282 2018    2   Call Center 24000   1   5   2/1/2018    7/1/2018

2 answers

  • answered 2018-07-11 05:58 Chanukya

    Try using ISNULL

     DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);
    
    SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.[Offer_cover]) 
                FROM #Table1 c 
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    set @query = 'SELECT Ref,role_name,offer_id, ' + isnull(@cols,0) + ' from 
                (
                    select*
                    from #Table1 
               ) x
                pivot 
                (
                     SUM(cover_earning_Count)
                    for [offer_cover] in (' + @cols + ') 
                ) p   '
    execute(@query)
    

  • answered 2018-07-11 06:16 Squirrel

    as i have mention earlier in my comments, you need 2 @cols, one for the select and another for the pivot

     DECLARE @cols   AS NVARCHAR(MAX),   -- for pivot
             @cols2  AS NVARCHAR(MAX),   -- for select
             @query  AS NVARCHAR(MAX);
    
    SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.[Offer_cover]) 
                FROM #cover2 c 
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    
    -- this is for the SELECT
    SET @cols2 = STUFF((SELECT distinct ',' + 'ISNULL(' + QUOTENAME(c.[Offer_cover]) + ', 0) ' + QUOTENAME(c.[Offer_cover])
                FROM #cover2 c 
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    
    set @query = 'SELECT Ref,role_name,offer_id, ' + @cols2 + ' from 
                (
                    select *
                    from #cover2 
                ) x
                pivot 
                (
                     SUM(cover_earning_Count)
                     for [Offer_cover] in (' + @cols + ') 
                ) p'
    
    -- do a print to verify the query
    print @query