PIVOT Help! - 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 Help!

Author

Topic

jxt1303
Starting Member

1 Post

Posted - 2010-09-03 : 13:50:45

So I have a table that has two fields, visit_id, and icd_code. There can be one several rows with the same visit_id but with different icd_code(s). What I need is for the data to be displayed horizonally instead of vertically. The PIVOT function seems like it could do this, but I don't have all of the parameters needed for that. Help!!

What the data looks like now (pretend the commas are column breaks):
VISIT_ID, ICD_CODE
212, 85.25
212, 74.25
245, 87.36
245, 75.21

What I want it to look like:
VISIT_ID, ICD1, ICD2, ICD3....
212, 85.25, 74.25
245, 87.36, 75.21

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-03 : 14:07:03

Proof of concept, change table or column names as needed:

create table #a (VISIT_ID int, ICD_CODE decimal(8,2))
insert #a select 
212, 85.25 union all select 
212, 74.25 union all select 
245, 87.36 union all select 
245, 75.21

;with a(visit_id, icd_code, rn) as 
(select visit_id, icd_code, row_number() over (partition by visit_id order by icd_code desc) rn from #a)
select visit_id, [1] ICD1, [2] ICD2, [3] ICD3, [4] ICD4, [5] ICD5 
from a
pivot (max(icd_code) for rn in ([1],[2],[3],[4],[5])) b

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-06 : 04:50:41

For unknown number of icd_code, use
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources