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

如何将两行数据和成一行！！急
表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 code
select 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


最简单的