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.
| Author |
Topic |
|
jmpierce30
Starting Member
7 Posts |
 Posted - 2009-12-02 : 14:51:08
|
| Hello I have a simple question, or may not be that simple, this is a sample of my data set in sql 2005.ID Code Priority-- ---- --------10 A1 110 A6 410 A3 620 A2 220 A4 320 A5 5What I need to get for output is to transform this table into the followingID Code1 Code2 Code3-- ----- ----- -----10 A1 A6 A320 A2 A4 A5So basically i need to assign codes by their priority number per ID number in the table. Currently this is being taken care of by a cursor but i'm wondering if there is a faster, easier way to do this.Any help would be greatly appreciated, thank you for your time.while (I != Understand){KickMe.Hard;} |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2009-12-02 : 15:41:31
|
| This doesn't consider possibility of tiesDECLARE @Table TABLE(ID int,COde char(2),Priority int)insert into @table SELECT 10,'A1', 1 UNION ALLSELECT 10,'A6', 4 UNION ALLSELECT 10,'A3', 6 UNION ALLSELECT 20,'A2', 2 UNION ALLSELECT 20,'A4', 3 UNION ALLSELECT 20,'A5', 5 SELECT id ,[Code1] = max(case when rank =1 then code end) ,[Code2] = max(case when rank =2 then code end) ,[Code3] = max(case when rank =3 then code end)FROM (select id,code,[Rank] = rank() over(partition by id order by priority) from @table ) agroup by idJimEveryday I learn something that somebody else already knew |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
Assigning Codes Via Priority