将一个表的二条记录合并成一条记录,该怎么处理

将一个表的二条记录合并成一条记录
a 表
id name  
1 china  

b表
name value
china v1 
china v2

查询
select a.*,b.value from a,b where a.name=b.name

结果
1 china v1
1 china v2

我想要的结果是
1 chna v1v2
就是将b表的value合并一条记录

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

create function f_str(@name varchar(20))
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret+','+value from b where name = @name 
set @ret = stuff(@ret,1,1,'')
return @ret 
end
go

--执行
select a.name,b.value=dbo.f_str(b.name) from b,a where b.name=a.name group by a.name order by a.name
------解决方案--------------------

SQL code

declare @ta table (name nvarchar(20),value nvarchar(10))
insert into @ta select 'china','v1'
insert into @ta select 'china','v2'
declare @tb table (id int,name nvarchar(20))
insert into @tb select 1,'china'

declare @sql nvarchar(1000)
select @sql=isnull(@sql,'')+','+value  from @ta 
set @sql = stuff(@sql,1,1,'')

select b.id,b.name,@sql from @ta a left join @tb b
on a.name =b.name group by b.id,b.name
id          name                 
----------- -------------------- -------------------
1           china                v1,v2

(1 行受影响)