--建立函数
create function f_Names(@Type varchar(10))
returns varchar(300)
as
begin
declare @r varchar(300)
set @r=''
select @r=@r+Name+'+' from tablename where type=@Type
if @r<>''
set @r=left(@r,len(@r)-1)
return @r
endgo---调用
select distinct dbo.f_Names(type) as name,type from tablename
create function f_Names(@Type varchar(10))
returns varchar(300)
as
begin
declare @r varchar(300)
set @r=''
select @r=@r+Name+'+' from tablename where type=@Type
if @r<>''
set @r=left(@r,len(@r)-1)
return @r
endgo---调用
select distinct dbo.f_Names(type) as name,type from tablename
create function f_str(@type char(1))
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+'+'+name from 表 where type=@type
return(substring(@r,2,8000))
end
go--调用实现查询
select name=dbo.f_str(type),type from 表 group by type
create table 表(id int,name varchar(10),type char(1))
insert 表 select 1,'abc','x'
union all select 2,'bcd','x'
union all select 3,'aa' ,'y'
go--创建处理函数
create function f_str(@type char(1))
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+'+'+name from 表 where type=@type
return(substring(@r,2,8000))
end
go--调用实现查询
select name=dbo.f_str(type),type from 表 group by type
go--删除测试
drop table 表
drop function f_str/*--测试结果name type
-------------------- ----
abc+bcd x
aa y(所影响的行数为 2 行)--*/