create function f_str(@MainId varchar(20))
returns varchar(8000)
as
begin
declare @ret varchar(8000)
select @ret=isnull(@ret,'')+'''+subname from sub where MainId =@name
set @ret=stuff(@ret,1,',','')
return @ret
endgoselect MainId,MainName.dbo.f_str(MainId) from Maingo drop function dbo.f_str
returns varchar(8000)
as
begin
declare @ret varchar(8000)
select @ret=isnull(@ret,'')+'''+subname from sub where MainId =@name
set @ret=stuff(@ret,1,',','')
return @ret
endgoselect MainId,MainName.dbo.f_str(MainId) from Maingo drop function dbo.f_str
returns varchar(8000)
as
begin
declare @ret varchar(8000)
select @ret=isnull(@ret,'')+'''+subname from sub where MainId =@MainId
set @ret=stuff(@ret,1,',','')
return @ret
endgoselect MainId,MainName.dbo.f_str(MainId) from Maingo drop function dbo.f_str
select MainId,MainName,dbo.f_str(MainId) from Main
(
MainId int,
MainName varchar(20)
)
create table Sub
(
SubId int,
MainId int,
SubName varchar(30)
)
insert Main
select 1,'甲单位' union
select 2,'乙单位'
insert Sub
select 1,1,'A人员' union
select 2,1,'B人员' union
select 3,2,'C人员' union
select 4,2,'D人员' union
select 5,2,'E人员'
go--创建函数
create function f_str(@MainId int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+SubName from Sub where MainId=@MainId
return stuff(@str,1,1,'')
end
go--查询
select *
,dbo.f_str(MainId) as ' SubName'
from Main--删除测试环境
drop function f_str
drop table Main,Sub--结果
/*
MainId MainName SubName
----------- -------------------- -------------
1 甲单位 A人员,B人员
2 乙单位 C人员,D人员,E人员(2 row(s) affected)
*/
returns varchar(8000)
as
begin
declare @ret varchar(8000)
select @ret=isnull(@ret,'')+','+isnull(subname,'') from sub where MainId =@MainId
set @ret=stuff(@ret,1,1,'')
return @ret
end