Posted: March 4, 2015 | Author: Jingyang Li | Filed under: Uncategorized |
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
Posted: March 4, 2015 | Author: Jingyang Li | Filed under: Uncategorized |
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
Jingyang Li 