CODEWITHSUNDEEP
sqlsql-server
WebDevGuy
WebDevGuy

Reputation: 353

Need assistance with PIVOT table

I have a SQL query that I need to convert into a PIVOT table. Right now the data appears as such...

enter image description here

But I really want it to appear with the ProcessDesc as COLUMN HEADINGS and the "intersection" being DateCompleted, such as....

enter image description here

How can I do this exactly? The query that creates the original query is as follows...

SELECT DISTINCT E.DisplayName, EPM.DateCompleted, PS.SortNumber, PS.ProcessDesc
FROM dbo.EPM
  INNER JOIN dbo.PS
    ON EPM.EPS = PS.ID
  INNER JOIN dbo.E
    ON EPM.ID = E.ID
WHERE DisplayName IS NOT NULL
ORDER BY E.DisplayName, PS.SortNumber

Upvotes: 0

Views: 38

Answers (2)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Try this one:

#TEMP here will be the one generated by your query:

SELECT DISTINCT E.DisplayName, EPM.DateCompleted, PS.SortNumber, PS.ProcessDesc
FROM dbo.EPM
  INNER JOIN dbo.PS
    ON EPM.EPS = PS.ID
  INNER JOIN dbo.E
    ON EPM.ID = E.ID
WHERE DisplayName IS NOT NULL
ORDER BY E.DisplayName, PS.SortNumber

SOLUTION USING DYNAMIC SQL

declare @sql1 as varchar(4000) = ''
declare @sql2 as varchar(4000) = ''
declare @sql3 as varchar(4000) = ''

set @sql1 = 
'select
    DisplayName
'

select @sql2 = @sql2 +
'   ,max(case when ProcessDesc = ''' + ProcessDesc + ''' then DateCompleted end) as [' + ProcessDesc +']
'
from #TEMP
order by ProcessDesc, ProcessSort

set @sql3 =
'
from #TEMP
group by DisplayName
ORDER BY DisplayName
'
print @sql1 + @sql2 +@sql3
exec(@sql1 + @sql2 +@sql3)

Upvotes: 1

Mihai
Mihai

Reputation: 26784

SELECT E.DisplayName, 
MAX(CASE WHEN PS.ProcessDesc='Set-Up Form 1' THEN EPM.DateCompleted END) as [Set-Up Form 1],
MAX(CASE WHEN PS.ProcessDesc='Set-Up Form 2' THEN EPM.DateCompleted END) as [Set-Up Form 2],
MAX(CASE WHEN PS.ProcessDesc='Set-Up Form 3' THEN EPM.DateCompleted END) as [Set-Up Form 3] 
FROM dbo.EPM
  INNER JOIN dbo.PS
    ON EPM.EPS = PS.ID
  INNER JOIN dbo.E
    ON EPM.ID = E.ID
WHERE DisplayName IS NOT NULL
GROUP BY E.DisplayName
ORDER BY E.DisplayName, PS.SortNumber

Upvotes: 0

Related Questions