Help with Pivot - SQL Server Forums

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)

Help with Pivot

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2012-02-03 : 08:48:29

I'm experimenting with the PIVOT function for the first time and can't quite get this right. Here are my three tables

and I want the results to look like this

So far I have this (View_1 joins the 3 tables)

SELECT ACS, SallieMae, FedLoan
FROM (
SELECT Servicer, Comments, Status, Measure
FROM View_1) up
PIVOT (MAX(MEasure) FOR Servicer IN (ACS, SallieMae, FedLoan)) AS pvt

but it looks like this

The number of Servicers (ACS, SallieMae, FedLoan) is fixed but the number of Measures (SDCL, Call Monitoring etc) is variable

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-03 : 09:41:13

[code]
SELECT *
FROM
(
SELECT s.Servicer,m.Measure,c.Comments
FROM Service s
INNER JOIN Comments c
ON c.ServiceID = c.ServiceID
INNER JOIN Measures m
ON m.MeasureID = c.MeasureID
)t
PIVOT(MAX(Comments) FOR Servicer IN (ACS, SallieMae, FedLoan))p
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources