动态透视，将 NULL 转换为 0

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来实现这一点。我试过在fieldname case语句中添加代码，它没有改变预期的效果(注释掉的区域是我的尝试)。

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

我只需要这些NULL到0。

Answer 1

而不是:

DECLARE 
@columns NVARCHAR(MAX) = '',
...

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

执行以下操作(基本上，创建两个不同的逗号分隔列表，其中一个包含COALESCE，以便在输出期间将0交换为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),'

然后在SELECT列表中使用@OutputColumns，在PIVOT列表中使用@PivotColumns。

这里是一个简单的例子，因为没有人会试图重现你的整个查询:

• db&lt例子;在小提琴

我在那里使用了前导逗号而不是尾随逗号，因为使用STUFF更容易删除第一个实例。