We help IT Professionals succeed at work.
Get Started

SQL Pivot - Results GROUP BY

625 Views
Last Modified: 2012-05-11
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
Topic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE