if object_id('pubs..tb') is not null
drop table tb
go
create table tb(id varchar(10),name varchar(10))
insert into tb(id,name) values('1', 'a')
insert into tb(id,name) values('1', 'b')
insert into tb(id,name) values('2', 'a')
insert into tb(id,name) values('2', 'b')
insert into tb(id,name) values('2', 'c')
insert into tb(id,name) values('2', 'd')
insert into tb(id,name) values('3', 'a')
insert into tb(id,name) values('3', 'b')
insert into tb(id,name) values('3', 'c')
insert into tb(id,name) values('3', 'd')
go
if object_id('pubs..f_hb') is not null
drop function f_hb
go
--创建一个合并的函数
create function f_hb(@id varchar)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + cast(name as varchar) from tb where id = @id
set @str = right(@str , len(@str))
return(@str)
End
go
--调用自定义函数得到结果:
select distinct id ,dbo.f_hb(id) as name from tb
create function fmerg(@col varchar(20))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+cast(col2 as varchar) from t where col1=@col
group by col2
return(@str)
End
go
create table t(col1 int,col2 varchar(10))
insert t select 1,'a'
union all select 1,'b'
union all select 2,'a'
union all select 2,'b'
union all select 2,'c'
union all select 2,'d'
union all select 3,'a'
union all select 3,'b'
union all select 3,'c'
union all select 3,'d'
go
create table tb(id int,col char(1))
insert tb
select 1,'a'
union all select 1,'b'
union all select 2,'a'
union all select 2,'b'
union all select 2,'c'
union all select 2,'d'
union all select 3,'a'
union all select 3,'b'
union all select 3,'c'
union all select 3,'d'
go
create function fn_strunion(@id int)
returns varchar(8000)
as
begin
declare @re varchar(8000)
set @re=''
select @re=@re+col from tb where id=@id
return @re
end
go
select id,col=dbo.fn_strunion(id) from tb group by id