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.
CodePudding user response：
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:
I went with leading commas there instead of trailing commas, since it's easier to remove the first instance using STUFF.