Dynamic Pivot, convert NULLs to 0's

Question

I have the following code:

DECLARE 
    @columns NVARCHAR(MAX) = '', 
    @sql     NVARCHAR(MAX) = '';
    
-- select the column names
SELECT 
    @columns += QUOTENAME(s.fullColName) + ','
FROM (SELECT  Case      
                WHEN g.MultiSelection = 0 THEN CONCAT(LEFT(c.[Name],62), ' - ', LEFT(g.[Name],62))
                WHEN g.MultiSelection = 1 THEN CONCAT(LEFT(c.[Name],40), ' - ', LEFT(g.[Name],40), ' - ', LEFT(f.[Name],40))
              END AS fullColName
             ,g.ReportVersionNum, g.[Name] AS gName ,c.[Name] AS cName
FROM
    (   
    SELECT ReportItemCategoryNum, ReportItemGroupNum, ReportVersionNum, [Name], MultiSelection -- If you would like to get rid of the '' in the columns you can use Replace(Name,'''','') AS Name
    FROM iCarolData.dbo.treportitemgroups
    WHERE ReportVersionNum = 60000
    ) g
    /* Join categories */
    LEFT JOIN
    (SELECT ReportItemCategoryNum, Name -- If you would like to get rid of the '' in the columns you can use Replace(Name,'''','') AS Name
      FROM iCarolData.dbo.treportItemCategories
      WHERE ReportVersionNum = 60000) c
    ON g.ReportItemCategoryNum = c.ReportItemCategoryNum
    /* Join Fields */
    Left Join
    (SELECT ReportItemFieldNum,ReportItemGroupNum,ReportItemCategoryNum, Name -- If you would like to get rid of the '' in the columns you can use Replace(Name,'''','') AS Name
      FROM iCarolData.dbo.treportitemfields
      WHERE ReportVersionNum = 60000) f
    ON g.ReportItemGroupNum = f.ReportItemGroupNum) s
WHERE gName != '...'
    AND cName != '...'
GROUP BY s.fullColName 
      --print @columns 

 /* remove the last comma */
SET @columns = LEFT(@columns, LEN(@columns) - 1);

/* construct dynamic SQL */
SET @sql ='
/* INTO Statement adds data to table */
SELECT * -- INTO cCareTeam
FROM   
(
    SELECT top (100) PERCENT
         Case       
            WHEN groups.MultiSelection = 0 THEN CONCAT(LEFT(cat.[Name],62), '' - '', LEFT(groups.[Name],62))
            WHEN groups.MultiSelection = 1 THEN CONCAT(LEFT(cat.[Name],40), '' - '', LEFT(groups.[Name],40), '' - '', LEFT(fields.[Name],40)) 
        END AS fullColName
        ,repItems.CallReportNum
        ,CASE 
            WHEN repItems.ReportItemFieldNum != -1 AND groups.MultiSelection = 0 THEN fields.Name
            --WHEN groups.MultiSelection = 0 AND fields.Name IS NULL THEN ISNULL(fields.[Name],''0'')
            WHEN repItems.ReportItemFieldNum != -1 AND groups.MultiSelection = 1 AND fields.Name IS NOT NULL THEN ''1''
            --WHEN groups.MultiSelection = 1 AND fields.Name IS NULL THEN ISNULL(fields.[Name],''0'')
            --ELSE ''0''
        END AS fieldName
        ,child.ChildOfCallReportNum
    FROM 
        (SELECT *
        /* report itemNums */
        FROM iCarolData.dbo.treportitems
        WHERE IsFinalized = 1) repItems
        /* Join default report data */
        LEFT JOIN iCarolData.dbo.treports defRep
        ON repItems.callReportNum = defRep.callreportNum
        /* Join Report Version map */
        LEFT JOIN iCarolData.dbo.treportsversion vers
        ON defRep.ReportVersionNum = vers.ReportVersionNum
        /* Join Categories */
        LEFT JOIN iCarolData.dbo.treportItemCategories cat
        ON repItems.ReportItemCategoryNum = cat.ReportItemCategoryNum
        /* Join Groups (questions) */
        LEFT JOIN iCarolData.dbo.treportitemgroups groups
        ON repItems.ReportItemGroupNum = groups.ReportItemGroupNum
        /* Join Fields (answers) */
        LEFT JOIN iCarolData.dbo.treportitemfields fields
        ON repitems.ReportItemFieldNum = fields.ReportItemFieldNum
        /* Join children report map */
        LEFT JOIN iCarolData.dbo.tReportsChildren child
        ON repItems.CallReportNum = child.CallReportNum
    WHERE
        groups.ReportVersionNum = 60000
    GROUP BY
         repItems.CallReportNum
         ,CONCAT(LEFT(cat.[Name],62), '' - '', LEFT(groups.[Name],62))
         ,CONCAT(LEFT(cat.[Name],40), '' - '', LEFT(groups.[Name],40), '' - '', LEFT(fields.[Name],40))
         ,fields.[Name],repItems.ReportItemFieldNum
        ,fields.Name
        ,child.ChildOfCallReportNum,groups.MultiSelection
) t 

PIVOT(
    MAX(fieldName) 
    FOR fullColName IN ('+ @columns +')
) AS pivot_table;';

-- execute the dynamic SQL
EXECUTE sp_executesql @sql;

The pivot works great however it produces null values that I need to eliminate dynamically. They have thousands of columns and are constantly adding columns to their dataset set so doing this through dynamic SQL is a must. I have tried adding code in the fieldname case statement and it doesn't change have the desired effect (the commented out areas was my attempt).

CASE 
            WHEN repItems.ReportItemFieldNum != -1 AND groups.MultiSelection = 0 THEN fields.Name
            --WHEN groups.MultiSelection = 0 AND fields.Name IS NULL THEN ISNULL(fields.[Name],''0'')
            WHEN repItems.ReportItemFieldNum != -1 AND groups.MultiSelection = 1 AND fields.Name IS NOT NULL THEN ''1''
            --WHEN groups.MultiSelection = 1 AND fields.Name IS NULL THEN ISNULL(fields.[Name],''0'')
            --ELSE ''0''
        END AS fieldName

Here is an example of what I'm getting:

col1    col2    col3    col4    col5    col6
NULL    NULL    NULL    NULL    NULL    NULL
NULL    NULL    NULL    NULL    NULL    NULL
NULL    NULL    NULL    NULL    NULL    NULL
NULL    NULL    NULL    NULL    NULL    NULL
NULL    NULL    NULL    NULL    NULL    NULL
Yes     NULL    NULL    NULL    NULL    NULL
No      NULL    NULL    NULL    NULL    NULL
NULL    NULL    NULL    NULL    NULL    NULL
NULL    NULL    1   NULL    1   1
NULL    NULL    1   NULL    1   1
NULL    NULL    1   NULL    1   1
NULL    NULL    1   NULL    1   1
NULL    NULL    1   NULL    1   1
NULL    NULL    1   NULL    1   1
NULL    NULL    1   NULL    1   1
NULL    NULL    NULL    NULL    NULL    NULL
NULL    NULL    NULL    NULL    NULL    NULL
NULL    NULL    NULL    NULL    NULL    NULL

I simply need those NULL's to 0's.

Aaron Bertrand · Accepted Answer · 2022-06-14T18:12:32.897

Instead of:

DECLARE 
    @columns NVARCHAR(MAX) = '',
    ...
    
-- select the column names
SELECT 
    @columns += QUOTENAME(s.fullColName) + ','

Do this (basically, create two different comma-separated lists of commas, one with the COALESCE necessary to swap in 0 for NULL during output, and one that is just the column names to list in the PIVOT):

DECLARE 
    @PivotColumns  nvarchar(max) = N'',
    @OutputColumns nvarchar(max) = N'',
    ...
    
-- select the column names
SELECT 
    @PivotColumns  += QUOTENAME(s.fullColName) + ',',
    @OutputColumns += QUOTENAME(s.fullColName) + ' = COALESCE('
                   +  QUOTENAME(s.fullColName) + ', 0),'

Then use @OutputColumns in the SELECT list, and @PivotColumns in the PIVOT.

Simplified example here, since there is no way anyone will try to reproduce your entire query:

Example db<>fiddle

I went with leading commas there instead of trailing commas, since it's easier to remove the first instance using STUFF.

This worked with one small change, I had to put double quotes around the 0. I forgot to mention in my original question that there is some string data being pivoted as well. Thank you for your guidance. — Jeff Perry, Jun 14 '22 at 19:30

Dynamic Pivot, convert NULLs to 0's

1 Answers1

Linked