--创建一个查询处理函数
create function f_str(
@集体 varchar(1000)
)returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=','+@集体+','
select @r=replace(@r,','+cast(编号 as varchar)+',',','+姓名+',')
from table1
where charindex(','+cast(编号 as varchar)+',',@r)>0
return(substring(@r,2,len(@r)-2))
end
go--调用函数实现查询
select 编号,集体=dbo.f_str(集体) from table2
create function f_str(
@集体 varchar(1000)
)returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=','+@集体+','
select @r=replace(@r,','+cast(编号 as varchar)+',',','+姓名+',')
from table1
where charindex(','+cast(编号 as varchar)+',',@r)>0
return(substring(@r,2,len(@r)-2))
end
go--调用函数实现查询
select 编号,集体=dbo.f_str(集体) from table2
create table table1(编号 int,姓名 varchar(10))
insert table1 select 1,'张三'
union all select 2,'李四'
union all select 3,'王五'create table table2(编号 int,集体 varchar(10))
insert table2 select 1,'1,2'
union all select 2,'2,3'
union all select 3,'1,2,3'
go--创建一个查询处理函数
create function f_str(
@集体 varchar(1000)
)returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=','+@集体+','
select @r=replace(@r,','+cast(编号 as varchar)+',',','+姓名+',')
from table1
where charindex(','+cast(编号 as varchar)+',',@r)>0
return(substring(@r,2,len(@r)-2))
end
go--调用函数实现查询
select 编号,集体=dbo.f_str(集体) from table2
go--删除测试
drop table table1,table2
drop function f_str/*--测试结果编号 集体
----------- -------------------
1 张三,李四
2 李四,王五
3 张三,李四,王五(所影响的行数为 3 行)
--*/