create function f_str(@fangabh varchar(20))
returns varchar(8000)
as
begin
declare @str varchar(80000
set @str=''
select @str=@str+','+duifname from 表二
where duifbsh in (
select duifbsh from 表一
where
fangabh=@fangabh and
yishj='否' and
shenhe<>'是'
) if @str is null
return '' return stuff(@str,1,1,'')
end
go--查询
select fangabh,
[duifname]=dbo.f_str(fangabh)
from 表一
where yishj='否' and shenhe<>'是'
group by fangabh
returns varchar(8000)
as
begin
declare @str varchar(80000
set @str=''
select @str=@str+','+duifname from 表二
where duifbsh in (
select duifbsh from 表一
where
fangabh=@fangabh and
yishj='否' and
shenhe<>'是'
) if @str is null
return '' return stuff(@str,1,1,'')
end
go--查询
select fangabh,
[duifname]=dbo.f_str(fangabh)
from 表一
where yishj='否' and shenhe<>'是'
group by fangabh
create function getstr(@fangabh nvarchar(4000))
returns Nvarchar(4000)
as
begin
declare @str Nvarchar(2000)
set @str=N''
select @str=@str+rtrim(x.duifname)+N',' from (select a.fangabh,b.duifname from tiaojfafz a,lsdtxb b where a.duifbsh=b.duifbsh and a.yishj='否'and a.shenhe<>'是') x group by where
x.fangabh= @fangabh
if @str<>N''
set @str=left(@str,len(@str)-1)
else
set @str=''
return @str
end
然后调用这个函数
select a.fangabh,dbo.getstr(a.fangabh) from tiaojfafz a,lsdtxb b where a.duifbsh=b.duifbsh and a.yishj='否'and a.shenhe<>'是' group by a.fangabh