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)
 Rows to Columns Results

Author  Topic 

infodemers
Posting Yak Master

183 Posts

Posted - 2013-02-11 : 13:58:15
I have the following results from my query.
Switch Lead Prefix Fnum Lnum NumberOfOE MDAType
ALMA 418-256 LDOR 1 1 9 WKG-NA
ALMA 418-256 LDOR 2 0 8 WKG-NA
ALMA 418-256 LDOR 2 1 2 WKG-NA
ALMA 418-256 LDOR 3 0 8 WKG-NA
ALMA 418-256 LDOR 3 1 3 WKG-NA
ALMA 418-256 LDOR 6 0 1 WKG-NA
ALMA 418-256 LDOR 7 0 2 WKG-NA
ALMA 418-256 LDOR 7 0 1 WKG-PA
ALMA 418-256 LDOR 7 1 1 ADV


And I wish to come to something like this:
Switch Lead Prefix Fnum Lnum WKG-NA WKG-PA ADV
ALMA 418-256 LDOR 1 1 9 0 0
ALMA 418-256 LDOR 2 0 8 0 0
ALMA 418-256 LDOR 2 1 2 0 0
ALMA 418-256 LDOR 3 0 8 0 0
ALMA 418-256 LDOR 3 1 3 0 0
ALMA 418-256 LDOR 6 0 1 0 0
ALMA 418-256 LDOR 7 0 2 1 0
ALMA 418-256 LDOR 7 1 0 0 4

It is like a pivot on columns NumberOfOE and MDAType...
Any Idea?
Thanks!

infodemers
Posting Yak Master

183 Posts

Posted - 2013-02-11 : 15:55:58
I got it....
I Inserted previous results into a @Results table and then....

SELECT * FROM @Results pivot(max([NumberOfOE]) FOR MDAType IN ([WKG-NA],[WKG-PA],[ADV])) As final

Regards!
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-02-12 : 05:59:49
For dynamic number of MDAType values, use http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -