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

SSIS and Import/Export (2005)

Pivot Transform

Author

Topic

sijeffs
Starting Member

1 Post

Posted - 2008-08-06 : 07:15:44

Hi All,

First topic so hopefully its understandable!

I need to transpose a set of values similar to below

RRH_KEY RRS_KEY
363044 92105
363044 92106
363044 92107
363044 92108
363044 92109
363044 92110
363045 92111
363045 92112
363045 92113
363045 92115
363045 92116

To give me a table that looks like below

RRH_KEY RRS_KEY_1 RRS_KEY_2 RRS_KEY_3 RRS_KEY_4 RRS_KEY_5 RRS_KEY_6
363044 92105 92106 92107 92108 92109 92110
363045 92111 92112 92113 92114 92115 92116

I've tried multiple settings with no joy. Do I need to rank the RRS_KEYS in order to get the pivot to work?

Going round in circles.

Any assistance greatly appreciated.

Sijeffs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-06 : 13:55:19

[code]SELECT t.RRH_KEY,
SUM(CASE WHEN Seq=1 THEN RRS_KEY ELSE 0 END) AS RRS_KEY_1,
SUM(CASE WHEN Seq=2 THEN RRS_KEY ELSE 0 END) AS RRS_KEY_2,
SUM(CASE WHEN Seq=3 THEN RRS_KEY ELSE 0 END) AS RRS_KEY_3,
SUM(CASE WHEN Seq=4 THEN RRS_KEY ELSE 0 END) AS RRS_KEY_4,
SUM(CASE WHEN Seq=5 THEN RRS_KEY ELSE 0 END) AS RRS_KEY_5,
SUM(CASE WHEN Seq=6 THEN RRS_KEY ELSE 0 END) AS RRS_KEY_6

FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY RRH_KEY ORDER BY RRS_KEY) AS Seq,*
FROM YourTable) t
GROUP BY t.RRH_KEY [/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-06 : 13:56:12

And if your number of RRS_KEY is not certain for each RRH_KEY and you want all of them then you need to use dynamic sql.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources