Split delimited column into separate ordered columns


 
 create table test(col1 varchar(10),col2 varchar(10),col3 varchar(100))
insert into test values('a','b','"1,2,3,4,5"'),('x','c','"5,1,3,1,3"')


 
 select col1, col2,IDENTITY(int, 1,1) id
 , substring(Replace(col3,'"',''), n, charindex(',', Replace(col3,'"','') + ',', n) - n)  cols
into mytemp
from test
cross apply (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20)) d(n) 
Where n <= len(Replace(col3,'"','')) AND substring(',' + Replace(col3,'"',''), n, 1) = ','
 


 ;with mycte as (Select * , row_number() Over(Partition by col1 Order by id ) rn 
 from mytemp
 )
 
 
 Select  Col1,col2
 , Max(Case when rn=1 then cols End) r1 
 , Max(Case when rn=2 then cols End) r2 
 , Max(Case when rn=3 then cols End) r3 
 , Max(Case when rn=4 then cols End) r4 
 , Max(Case when rn=5 then cols End) r5 
 
 from mycte
 Group by  Col1,col2
 
 drop table mytemp


drop table  test
 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s