--不知道这样合不合楼主要求。好像硬要在一行实现的话,基本不可能。
declare @str varchar(100)
set @str=''select @str=@str+','+title
from ta, tb
where ta.id=tb.id
and ta.id=1set @str=stuff(@str, 1, 1, '')
print @str
declare @str varchar(100)
set @str=''select @str=@str+','+title
from ta, tb
where ta.id=tb.id
and ta.id=1set @str=stuff(@str, 1, 1, '')
print @str
CREATE FUNCTION [fdd] (@ID int)
RETURNS varCHAR(100) AS
BEGIN
declare @str varchar(100)
set @str=''select @str=@str+','+title
from ta, tb
where ta.id=tb.id
and ta.id=1set @str=stuff(@str, 1, 1, '')RETURN @strEND--在视图中
select dbo.fdd(1) Title from ta
Create function F_inerStr(@id as int)
returns varchar(8000)
as
begin
declare @returnsValue as varchar(8000)
set @returnsValue =''
select @returnsValue=@returnsValue+','+ltrim(rtrim()) from
(select Title from 从表 where id=@id)
B
set @returnsValue =stuff(@returnsValue ,1,1,'')
return @returnsValue
end
--视图
Create view as Result
select id,F_inerStr(@id as int)
from 主表
create function dbo.f_Title
(@id int)
returns varchar(200)
begin
declare @s varchar(200)
set @s=''
select @s=@s+title+',' from 从表 where [id]=@id
select @s=left(@s,len(@s)-1)
return(@s)
end
--查询结果
select distinct dbo.f_Title([id]) from 从表
where [id]=1
因为得到的结果只是一个字符型变量。你将查询的结果放在一个字符型变量中,然后想怎么用就怎么用。declare @str varchar(100)
set @str=''select @str=@str+','+title
from ta, tb
where ta.id=tb.id
and ta.id=1set @str=stuff(@str, 1, 1,'')
select @str as '为1时的情况', *
from ta, tb
where ta.id=tb.id
and ta.id=1
Create function F_inerStr(@id as int)
returns varchar(8000)
as
begin
declare @returnsValue as varchar(8000)
set @returnsValue =''
select @returnsValue=@returnsValue+','+ltrim(rtrim(Title)) from
(select Title from 从表 where id=@id)
B
set @returnsValue =stuff(@returnsValue ,1,1,'')
return @returnsValue
end--再建视图
Create view Result
as
select id,dbo.F_inerStr(id) as Result
from 主表