I have a SQL Pivot that works, unless a column in the table that is not in the PIVOT contains a value. Here is the scenario:
Target Pivot Table:
MLAppName CIAppName MasterListServerID RelationshipStatus RelationshipAlert RelCustomValue
AllAssets LOGLOGIC 8ce5c808-39a5-4e48-bd81-4d
154cc840f1
True 1
AllAssets LOGLOGIC a53573b0-2ae2-4081-af3a-75
ae77e8d80f
True 1
AllAssets LOGLOGIC 27557038-b022-4b58-b7e4-cf
d8feb1eda5
True 0 192.168.1.3
AllAssets LOGLOGIC 9437a938-5ba9-4d22-bd84-e0
b5b35ff5af
True 1
AllAssets LOGLOGIC da03f5bb-32ab-4a8c-afd1-99
1ad05cb330
True 1
AllAssets LOGLOGIC c96487d3-f8d7-4531-a018-48
dd0a9572be
True 1
AllAssets LOGLOGIC edcf8178-8ee4-4aa5-aa05-b0
fa93789cac
True 1
AllAssets LOGLOGIC 53fb96a3-1bdf-4eaf-a814-fe
33c9f973d3
True 1
AllAssets LOGLOGIC cf171bd8-84c9-411b-a6bf-b1
e828c9ccee
True 1
Having the value in RelCustomValue is causing the rows not to group, and when I remove the value everything groups.
Here is the SQL:
DECLARE @columns VARCHAR(8000)
SELECT @columns = COALESCE(@columns + ',[' + cast(CIAppName as varchar) + ']',
'[' + cast(CIAppName as varchar)+ ']')
FROM dbo.MasterListServerProfile
WHERE dbo.MasterListServerProfile.MLAppName = @MLAppName
GROUP BY CIAppName
DECLARE @query VARCHAR(8000)
SET @query = 'IF OBJECT_ID (''dbo.SEARCHRESULTS'', ''U'') IS NOT NULL DROP TABLE dbo.DASHBOARD;
SELECT * INTO dbo.DASHBOARD
FROM dbo.MasterListServerProfile
PIVOT
(
SUM(RelationshipAlert)
FOR [CIAppName]
IN (' + @columns + ')
)
AS p'
EXECUTE(@query)
DECLARE @selectFromDash VARCHAR(8000)
SET @selectFromDash = 'SELECT dbo.MasterListServer.MLServerName AS [Master List Server Identifier], ' + @columns +
' FROM dbo.DASHBOARD
INNER JOIN dbo.MasterListServer ON dbo.DASHBOARD.MasterListServerID = dbo.MasterListServer.MasterListServerID ' + @WhereClause + ' GROUP BY dbo.MasterListServer.MLServerName, ' + @columns
EXECUTE(@selectFromDash)
Select all
Open in new window
I have tried use explicit column names, instead of SELECT * INTO dbo.DASHBOARD, but that did not fix the problem.
Here is the result of the query:
MLAppName MasterListServerID RelationshipStatus RelCustomValue LOGLOGIC SymantecBackupExec
AllAssets 27557038-b022-4b58-b7e4-cf
d8feb1eda5
1 NULL 0
AllAssets 27557038-b022-4b58-b7e4-cf
d8feb1eda5
1 192.168.1.3 0 NULL
AllAssets 53fb96a3-1bdf-4eaf-a814-fe
33c9f973d3
1 1 0
AllAssets 8ce5c808-39a5-4e48-bd81-4d
154cc840f1
1 1 1
AllAssets 9437a938-5ba9-4d22-bd84-e0
b5b35ff5af
1 1 0
AllAssets a53573b0-2ae2-4081-af3a-75
ae77e8d80f
1 1 0
AllAssets c96487d3-f8d7-4531-a018-48
dd0a9572be
1 1 0
AllAssets cf171bd8-84c9-411b-a6bf-b1
e828c9ccee
1 1 0
AllAssets da03f5bb-32ab-4a8c-afd1-99
1ad05cb330
1 1 0
AllAssets edcf8178-8ee4-4aa5-aa05-b0
fa93789cac
1 1 0
VMware 064ca08a-2b3c-413c-bd17-a5
92dd83ef69
1 0 1
VMware 10d763fd-1181-4efa-b446-44
53731308db
1 0 1
VMware 14543de9-9cb7-444c-9e6d-8b
7881012d2c
1 0 1
VMware 15db3d9a-bc11-471f-b665-2e
a45b178caf
1 0 1
VMware 46ca34d2-fcae-45a9-9dd7-d0
d6c5cc2de2
1 0 1
VMware 6d027f7d-c18b-4b1d-ad98-11
b0a4b77f22
1 0 1
VMware bb894458-71dc-4e3e-b9a5-97
878bf9997a
1 0 1
VMware e8bbbe9d-62d2-4333-8832-c4
f12caa5c29
1 0 1
VMware ea5bd8a3-a683-45e5-b427-2a
0e705cca38
1 0 1
When I remove the value from the RelCustomValue column, the NULL values do not appear, and those two rows group correctly. I am trying to ignore this column in the PIVOT, so the rows will group.