--创建用户定义函数 create function f_str(@id int) returns varchar(8000) as begin declare @ret varchar(8000) set @ret = '' select @ret = @ret+'+'+why from 表 where id = @id set @ret = stuff(@ret,1,1,'') return @ret end go --执行 select id,why=dbo.f_str(id) from 表 group by id order by id go
create table tb ( id int, why char(10) ) insert into tb(id,why) values(1,'a') insert into tb(id,why) values(1,'b') insert into tb(id,why) values(1,'c') insert into tb(id,why) values(2,'d') insert into tb(id,why) values(2,'b')go--创建一个合并的函数 create function f_hb(@id int) returns varchar(8000) as begin declare @str varchar(8000) set @str = '' select @str = @str + '+' + why from tb where id = @id set @str = right(@str , len(@str) - 1) return(@str) End go--调用自定义函数得到结果: select distinct id,why=dbo.f_hb(id) from tbdrop table tb
create table T(id int, why char(1)) insert T select 1, 'a' union all select 1, 'b' union all select 1, 'c' union all select 2, 'd' union all select 2, 'b'create function fun(@id int) returns varchar(1000) as begin declare @re varchar(1000) set @re='' select @re=@re+why+'+' from T where id=@id set @re=left(@re, len(@re)-1)
return @re endselect id, dbo.fun(id) as Why from T group by id--result id Why ----------- ------------------- 1 a+b+c 2 d+b(2 row(s) affected)
create function f_str(@id int)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret+'+'+why from 表 where id = @id
set @ret = stuff(@ret,1,1,'')
return @ret
end
go
--执行
select id,why=dbo.f_str(id) from 表 group by id order by id
go
(
id int,
why char(10)
)
insert into tb(id,why) values(1,'a')
insert into tb(id,why) values(1,'b')
insert into tb(id,why) values(1,'c')
insert into tb(id,why) values(2,'d')
insert into tb(id,why) values(2,'b')go--创建一个合并的函数
create function f_hb(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + '+' + why from tb where id = @id
set @str = right(@str , len(@str) - 1)
return(@str)
End
go--调用自定义函数得到结果:
select distinct id,why=dbo.f_hb(id) from tbdrop table tb
create table T(id int, why char(1))
insert T select 1, 'a'
union all select 1, 'b'
union all select 1, 'c'
union all select 2, 'd'
union all select 2, 'b'create function fun(@id int)
returns varchar(1000)
as
begin
declare @re varchar(1000)
set @re=''
select @re=@re+why+'+' from T where id=@id
set @re=left(@re, len(@re)-1)
return @re
endselect id, dbo.fun(id) as Why from T group by id--result
id Why
----------- -------------------
1 a+b+c
2 d+b(2 row(s) affected)