mysq 的 GROUP_CONCAT的sql server 实现

create table #tb(id int, value varchar(10)) 
insert into #tb values(1, 'aa') 
insert into #tb values(1, 'bb') 
insert into #tb values(2, 'aaa') 
insert into #tb values(2, 'bbb') 
insert into #tb values(2, 'ccc') 
go


select id, [value] = stuff((select ',' + [value] from #tb t where id = a.id for xml path('')) , 1 , 1 , '') 
from #tb a
group by id