Pivot rows into one line - 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)

Pivot rows into one line

Author

Topic

oasis1
Starting Member

35 Posts

Posted - 2012-01-31 : 18:41:07

I need to take a group of records

ID DXCode
2 123
2 456
2 789

and split it out as

ID DX1 DX2 DX3
2 123 456 789

mahalo,
mark

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-31 : 18:52:31

[code];WITH a AS
(
SELECT
*,ROW_NUMBER() OVER (PARTITION BY id ORDER BY dxcode) AS RN
FROM
YourTable
)
SELECT
*
FROM
a
PIVOT (MAX(dxcode) FOR RN IN ([1],[2],[3])) P[/code]If you have more than 3 DXCodes for a given ID, you will need to add more columns to the Pivot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-31 : 19:05:35

can there be only 3 values per ID? or do you want only 3 values always to be pivotted?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources