利用FOR XML PATH行转列（根据某字段分组，多行数据转成一行，并用逗号隔开）

create table #test
(a varchar(10) null,b varchar(max) null)

insert into #test
select ‘a’,’a001′
union all
select ‘a’,’a002′
union all
select ‘a’,’a003′
union all
select ‘b’,’b001′
union all
select ‘b’,’b002′

 

select * from #test

 

–先来看一个简单的，效果如下图所示：

select stuff((select ‘,’+b from #test for xml path(”)),1,1,”)

—————————————————————

–再来个我们想要的，效果见下图：
select distinct t1.a,
replace((select b as [data()] from #test t2 where t1.a=t2.a for xml path(”)),’ ‘,’,’) as b
from #test t1

—————————————————————