SQL Pivot - Results GROUP BY

frontback45
frontback45 used Ask the Experts™
on
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-4d154cc840f1      True      1      
AllAssets      LOGLOGIC      a53573b0-2ae2-4081-af3a-75ae77e8d80f      True      1      
AllAssets      LOGLOGIC      27557038-b022-4b58-b7e4-cfd8feb1eda5      True      0      192.168.1.3
AllAssets      LOGLOGIC      9437a938-5ba9-4d22-bd84-e0b5b35ff5af      True      1      
AllAssets      LOGLOGIC      da03f5bb-32ab-4a8c-afd1-991ad05cb330      True      1      
AllAssets      LOGLOGIC      c96487d3-f8d7-4531-a018-48dd0a9572be      True      1      
AllAssets      LOGLOGIC      edcf8178-8ee4-4aa5-aa05-b0fa93789cac      True      1      
AllAssets      LOGLOGIC      53fb96a3-1bdf-4eaf-a814-fe33c9f973d3      True      1      
AllAssets      LOGLOGIC      cf171bd8-84c9-411b-a6bf-b1e828c9ccee      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)

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-cfd8feb1eda5      1            NULL      0
AllAssets      27557038-b022-4b58-b7e4-cfd8feb1eda5      1      192.168.1.3      0      NULL
AllAssets      53fb96a3-1bdf-4eaf-a814-fe33c9f973d3      1            1      0
AllAssets      8ce5c808-39a5-4e48-bd81-4d154cc840f1      1            1      1
AllAssets      9437a938-5ba9-4d22-bd84-e0b5b35ff5af      1            1      0
AllAssets      a53573b0-2ae2-4081-af3a-75ae77e8d80f      1            1      0
AllAssets      c96487d3-f8d7-4531-a018-48dd0a9572be      1            1      0
AllAssets      cf171bd8-84c9-411b-a6bf-b1e828c9ccee      1            1      0
AllAssets      da03f5bb-32ab-4a8c-afd1-991ad05cb330      1            1      0
AllAssets      edcf8178-8ee4-4aa5-aa05-b0fa93789cac      1            1      0
VMware      064ca08a-2b3c-413c-bd17-a592dd83ef69      1            0      1
VMware      10d763fd-1181-4efa-b446-4453731308db      1            0      1
VMware      14543de9-9cb7-444c-9e6d-8b7881012d2c      1            0      1
VMware      15db3d9a-bc11-471f-b665-2ea45b178caf      1            0      1
VMware      46ca34d2-fcae-45a9-9dd7-d0d6c5cc2de2      1            0      1
VMware      6d027f7d-c18b-4b1d-ad98-11b0a4b77f22      1            0      1
VMware      bb894458-71dc-4e3e-b9a5-97878bf9997a      1            0      1
VMware      e8bbbe9d-62d2-4333-8832-c4f12caa5c29      1            0      1
VMware      ea5bd8a3-a683-45e5-b427-2a0e705cca38      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.

      
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Topic Advisor, Page Editor
Distinguished Expert 2018
Commented:
Best to select from a subquery of wanted columns first....


'SELECT * INTO dbo.DASHBOARD FROM
(select MLAppName, MasterListServerID,RelationshipAlert,CIAppName FROM dbo.MasterListServerProfile ) src
PIVOT
(SUM(RelationshipAlert) FOR [CIAppName] IN (' + @columns + ') ) pvt'

Open in new window

Author

Commented:
Perfect answer!  You are the man.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial