create table tb(id int,str varchar(100))
go
insert into tb
select 1,'aaa' union all
select 1,'bbb' union all
select 2,'ccc' union all
select 3,'ddd' union all
select 3,'eee' union all
select 3,'fff'
go
--写一个聚合函数:
create function dbo.fn_Merge(@id int)
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+str from tb where id=@id
return stuff(@r,1,1,'')
end
go
-- 调用函数
select id, dbo.fn_Merge(id) as str from tb group by id
--测试数据
create table csdn(id int,txt varchar(10))
insert csdn
select 1,'a' union all
select 1,'b' union all
select 1,'c' union all
select 2,'aa' union all
select 2,'bb' union all
select 2,'cc' union all
select 3,'aaa' union all
select 3,'bbb'
select * from csdn
go
create function Gettxt(@id int)
returns varchar(8000)
as
begin
declare @s varchar(8000)
set @s=''
select @s=@s +',' +txt from csdn where id=@id
--return @s
return stuff(@s,1,1,'')
end
go
select id,dbo.Gettxt(id) txt from csdn group by id
go