convert rows to columns dynamically

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 2005 Forums

Transact-SQL (2005)

convert rows to columns dynamically

Author

Topic

cjcclee
Starting Member

33 Posts

Posted - 2013-07-15 : 17:58:07

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

549 Posts

Posted - 2013-07-15 : 20:29:09

Duplicate of http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=186833

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-16 : 01:27:31

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources