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

Replace id value with name for a not normalized table

create table Invoices (Invoiceid int, date date, price int,userid varchar(50))
insert into Invoices values(1,'1/1/2015',35,'|5|6|9|'),(2,'3/4/2015',25,'|5|10|')
 

 create table  Users (userid int, userName varchar(50))
 Insert into Users values(5,'John'),(6,'Mike'),(9,'Tom'),(10,'Sara')


 --Select * from invoices


 ;With mycte as (
 select Invoiceid, date, price,  substring(userid, n, charindex('|', userid + '|', n) - n)  userid
from invoices
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(userid) AND substring('|' + userid, n, 1) = '|'
 
)
 
 ,mycte1 as (
 Select Invoiceid, date, price,username from mycte m join  users u on m.userid=u.userid
 )


 SELECT t1.Invoiceid,t1.date, t1.price,
       Stuff(( SELECT ';' + Cast(t2.username as varchar(5))
           FROM mycte1 t2
          WHERE t2.Invoiceid = t1.Invoiceid 
		  ANd username<>''
          ORDER BY t2.username
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS ids
  FROM mycte1 t1
 GROUP BY t1.Invoiceid ,t1.date, t1.price 


 drop table Invoices, users