动态枢轴，将nulls转换为0＆＃x27

Question

我有以下代码：

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;

枢轴效果很好，但是它会产生我需要动态消除的空值。他们有成千上万的列，并不断地向其数据集集添加列，因此必须通过动态SQL进行此操作。我已经尝试在“字面名称”案例语句中添加代码，但它不会改变所需的效果（我尝试了评论的区域）。

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  

这是我得到的一个示例：

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

我只需要那些零是0。

Answer 1

而不是：

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

执行此操作（基本上，创建两个不同的逗号分隔的逗号列表，一个用 cocece 交换 0 for null 所需在输出期间，仅在 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),'

然后在 @outputcolumns 中使用 select>选择 list和<代码> @pivotcolumns 在 pivot 中。

这里简化的示例，因为没有任何人可以尝试重现您的整个查询：

我在那儿带领逗号而不是落后逗号，因为使用 stuck 更容易删除第一个实例。