怎么将两行数据和成一行！_Sql Server

如何将两行数据和成一行！！急
表1
id name produce
1 锐气公司 pvc
2 锐气公司 amb
3 牡丹公司 pvc
用sql语句合成
id name produce
1 锐气公司 pvc,amb
2 牡丹公司 pvc

如何用比较简单的方法写呢？
是sql2005

------解决方案--------------------

SQL code

select name,produce=stuff((select ','+produce from table1 where name=T.name),1,1,'') from table1 T group by name
------解决方案--------------------
SQL codeselect min(id) id, name, [produce] = stuff((select ',' + [produce] from tb t where name = tb.name for xml path('')) , 1 , 1 , '')from tbgroup by name
------解决方案--------------------
SELECT id,name,
        produce= STUFF
        (
         (SELECT DISTINCT ',' + 物料名称
          from table1 b where b.id=c.id for xml path('')) , 1 , 1 , ''
        ) from table1 a
------解决方案--------------------
SQL code;with tas(select name,stuff(select ','+produce                  from tb                   where name=a.name for xml path(''))producefrom tb agroup by name)select row_number() over(order by name) id,*from t
------解决方案--------------------
SELECT id,name,
 produce= STUFF
 (
 (SELECT DISTINCT ',' + produce
 from table1 b where b.id=c.id for xml path('')) , 1 , 1 , ''
 ) from table1 a group by id,name
------解决方案--------------------
探讨

SQL code
select min(id) id, name, 
[produce] = stuff((select ',' + [produce] from tb t where name = tb.name for xml path('')) , 1 , 1 , '')
from tb
group by name


最简单的