创建一个合并的函数:
create function fmerge(@b int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+'、'+a from a where b=@b
set @str=right(@str,len(@str)-1)
return(@str)
End
go--调用自定义函数得到结果
select distinct a=dbo.fmerge(b),b from a
create function fmerge(@b int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+'、'+a from a where b=@b
set @str=right(@str,len(@str)-1)
return(@str)
End
go--调用自定义函数得到结果
select distinct a=dbo.fmerge(b),b from a
--用临时表
select a=cast(a as varchar(8000)),b into #t from 表A order by b
declare @r varchar(8000),@b int
update #t set @r=case when @b=b then @r+'、'+a else a end
,@b=b,a=@r
select a=max(a),b from #t group by b
drop table #t
create table 表A(a varchar(10),b int)
insert 表A select '张三',1
union all select '李四',1
union all select '王五',2
union all select '王八',2
go--用临时表
select a=cast(a as varchar(8000)),b into #t from 表A order by b
declare @r varchar(8000),@b int
update #t set @r=case when @b=b then @r+'、'+a else a end
,@b=b,a=@r
select a=max(a),b from #t group by b
drop table #t
go--删除测试
drop table 表A/*--测试结果a b
----------- -----
张三、李四 1
王五、王八 2(所影响的行数为 2 行)
--*/
from a c inner join a b
on c.b=b.b and c.a<>b.a
and c.a=(select top 1 a from A where b=c.b)