Friday, August 12, 2011

Transforming rows to a single column(Example)

create table temp( account int,rid char(2))
insert into temp(account,rid)VALUES
( '1000003', '01'),('1000003', '02'),('1000003' ,'03'),('1000003' ,'04'),('1000003' ,'05'),('1000003' ,'06')

SELECT p1.account,
( SELECT rid + ',' FROM temp p2 WHERE p2.account = p1.account ORDER BY rid FOR XML PATH('') ) AS acc FROM temp p1 GROUP BY account ;

No comments:

Post a Comment