T-SQL备忘(3)：分组合并

--CREATE TABLE test(code varchar(50), [name] varchar(10),[count]  int )
--INSERT test SELECT  '001' ,  'aa' ,1
--UNION ALL SELECT  '001' ,  'bb' ,2
--UNION ALL SELECT  '002' ,  'aaa' ,4
--UNION ALL SELECT  '002' ,  'bbb' ,5
--UNION ALL SELECT  '002' ,  'ccc' ,3;
 
select  code,names=LEFT(names,LEN(names)-1)  from  ( select  code,( select  name+ ','  from  test  where  code=t1.code FOR XML PATH( '' ))  as  names,COUNT(code)  as  count
from  test t1  group  by  code) t
 
select  code,names=substring(names,0,len(names))  from  ( select  code,( select  name+ ','  from  test  where  code=t1.code FOR XML PATH( '' ))  as  names,COUNT(code)  as  count
from  test t1  group  by  code) t
 
select  code, names=stuff(( select  ',' +name  from  test  where  code=t1.code FOR XML PATH( '' )),1,1, '' ),COUNT(code)  as  count
from  test t1  group  by  code