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 |
|
scabral7
Yak Posting Veteran
57 Posts |
 Posted - 2009-08-20 : 12:40:37
|
| Hi,i have a table like this:ID Priority Acct_no ICDCode1 1 123 1.01 3 123 1.51 4 123 2.02 1 234 1.82 2 234 1.53 1 456 3.73 3 456 4.13 5 456 4.03 7 456 3.33 8 456 3.6i need to insert into a table like this:ID Acct_no P1 ICDCd1 P2 ICDCd2 P3 ICDCd3 P4 ICDCd4 P5 ICDCd51 123 1 1.0 3 1.5 4 2.0 2 234 1 1.8 2 1.83 456 1 3.7 3 4.1 5 4.0 7 3.3 8 3.6there will always be max 5 priorities for each account. I think i can do this with a cursor (although i can't picture the syntax in my head). I wasn't sure if there was a set based way to do this to stay away from the cursor.Anyone have any ideas??thanksScott |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-08-20 : 22:11:01
|
[code]SELECT ID, Acct_no, P1 = MAX(CASE WHEN Priority = 1 THEN Priority END), ICDCd1 = MAX(CASE WHEN Priority = 1 THEN ICDCode END), P2 = MAX(CASE WHEN Priority = 2 THEN Priority END), ICDCd2 = MAX(CASE WHEN Priority = 2 THEN ICDCode END), P3 = MAX(CASE WHEN Priority = 3 THEN Priority END), ICDCd3 = MAX(CASE WHEN Priority = 3 THEN ICDCode END), P4 = MAX(CASE WHEN Priority = 4 THEN Priority END), ICDCd4 = MAX(CASE WHEN Priority = 4 THEN ICDCode END), P5 = MAX(CASE WHEN Priority = 5 THEN Priority END), ICDCd5 = MAX(CASE WHEN Priority = 5 THEN ICDCode END) FROM a_tableGROUP BY ID, Acct_no[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
|
|
|
|
|
transpose rows to columns