PIVOT and Crosstab difficulties

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

PIVOT and Crosstab difficulties

Author

Topic

SCHEMA
Posting Yak Master

192 Posts

Posted - 2012-04-22 : 10:28:22

quote:

Originally posted by SCHEMA

Here is my how table looks like


DisplayValue	        RID	                              Seq	Code   ColumnName	       Value
DegreeProgram	       AF48C244-8693-4FD4-9D01-AF5794CFD458	1	13	Column1	                20
Role	               AF48C244-8693-4FD4-9D01-AF5794CFD458	1	13	Column2	                 5
PopulationFoci	       AF48C244-8693-4FD4-9D01-AF5794CFD458	1	13	Column3	
DegreeProgram	       AF48C244-8693-4FD4-9D01-AF5794CFD458	2	13	Column1	                 10
Role	               AF48C244-8693-4FD4-9D01-AF5794CFD458	2	13	Column2	                 15
PopulationFoci	       AF48C244-8693-4FD4-9D01-AF5794CFD458	2	13	Column3	                 20
DegreeProgram	       AF48C244-8693-4FD4-9D01-AF5794CFD458	3	13	Column1	                 20
Role	               AF48C244-8693-4FD4-9D01-AF5794CFD458	3	13	Column2	                 5
PopulationFoci	       AF48C244-8693-4FD4-9D01-AF5794CFD458	3	13	Column3	
DegreeProgram	       71232BEC-3723-4B89-B626-657F14DD5426	1	13	Column1	                 15
Role	               71232BEC-3723-4B89-B626-657F14DD5426	1	13	Column2	                 20
PopulationFoci	       71232BEC-3723-4B89-B626-657F14DD5426	1	13	Column3

Here is my desired output


RID	                               Code	Seq	DegreeProgram	Role	PopulationFoci
71232BEC-3723-4B89-B626-657F14DD5426	13	1	15	20	
AF48C244-8693-4FD4-9D01-AF5794CFD458	13	1	20	5	
AF48C244-8693-4FD4-9D01-AF5794CFD458	13	2	10	15	20
AF48C244-8693-4FD4-9D01-AF5794CFD458	13	3	20	5

Simply I can use this query and get result

Select RID,Code,Seq,
MAX(Case When ColumnName = 'Column1' then Value Else null End) as [DegreeProgram],
MAX(Case When ColumnName = 'Column2' then Value Else null End) as [Role],
MAX(Case When ColumnName = 'Column3' then Value Else null End) as [PopulationFoci]
from bpmh.dbo.tmp_PVT
Group by RID,Code,Seq
Order by 1

But How to achieve the same with PIVOT Query? I am using PIVOT dynamically. Please help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-22 : 13:13:36

[code]
SELECT RID,Code,Seq,[Column1],[Column2],[Column3]
FROM
(
SELECT RID,Code,Seq,ColumnName,Value
FROM bpmh.dbo.tmp_PVT
)t
PIVOT ( MAX([Value]) FOR ColumnName IN ([Column1],[Column2],[Column3]))p
ORDER BY RID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources