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 2005 Forums
 Transact-SQL (2005)
 convert rows to columns dynamically
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cjcclee
Starting Member

33 Posts

Posted - 07/15/2013 :  17:58:07  Show Profile  Send cjcclee an AOL message  Reply with Quote
Hi, Dearl all,

I need help on this query. I have table shown as below,
each logID may have multiple types

LOGID Types
1 Value1
1 Value3
2 Value3
2 Value5
3 Value8
4 Value5

I used dynamic SQL using pivot, get the result as below:

LOGID Value1 Value3 Value5 Value8
1 Value1 Value3 null null
2 null Value3 Value5 null
3 null null null Value8
4 null null Value5 null

This is not the format I want, I want it display as following:

LOGID Column1 Columns2 Column3 Column4
1 Value1 Value3 null null
2 Value3 Value5 null null
3 Value8 null null null
4 Value5 null null null


How to do it? Thank you!



MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/15/2013 :  20:29:09  Show Profile  Reply with Quote
Duplicate of http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=186833
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/16/2013 :  01:27:31  Show Profile  Reply with Quote
you just need to do the pivotting like this to get intended result in single step


;With CTE
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY LOGID ORDER BY Types) AS Seq,*
FROM Table
)

SELECT *
FROM CTE c
PIVOT (MAX(Types) FOR Seq IN ([1],[2],[3],[4],[5]))p


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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