SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Rows to Columns Results
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

infodemers
Posting Yak Master

Canada
181 Posts

Posted - 02/11/2013 :  13:58:15  Show Profile  Reply with Quote
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!

Edited by - infodemers on 02/11/2013 14:32:33

infodemers
Posting Yak Master

Canada
181 Posts

Posted - 02/11/2013 :  15:55:58  Show Profile  Reply with Quote
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

India
22754 Posts

Posted - 02/12/2013 :  05:59:49  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000