将一个表的二条记录合并成一条记录
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
[解决办法]
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 bon a.name =b.name group by b.id,b.nameid name ----------- -------------------- -------------------1 china v1,v2(1 行受影响)
