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 ICDCode
1 1 123 1.0
1 3 123 1.5
1 4 123 2.0
2 1 234 1.8
2 2 234 1.5
3 1 456 3.7
3 3 456 4.1
3 5 456 4.0
3 7 456 3.3
3 8 456 3.6
i need to insert into a table like this:
ID Acct_no P1 ICDCd1 P2 ICDCd2 P3 ICDCd3 P4 ICDCd4 P5 ICDCd5
1 123 1 1.0 3 1.5 4 2.0
2 234 1 1.8 2 1.8
3 456 1 3.7 3 4.1 5 4.0 7 3.3 8 3.6
there 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??
thanks
Scott |
|
|
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_table
GROUP BY ID,
Acct_no
[/code]
KH
[spoiler]Time is always against us[/spoiler]
|
 |
|
|
|
|
|
transpose rows to columns