if object_id('temp') is not null
drop table [temp]
create table [temp](FId int,FName varchar(2))
insert into [temp] values(1, 'aa')
insert into [temp] values(1, 'bb')
insert into [temp] values(1, 'cc')
insert into [temp] values(1, 'dd')
insert into [temp] values(2, 'aa')
insert into [temp] values(2, 'bb')
insert into [temp] values(2, 'cc')
insert into [temp] values(3, 'aa')
insert into [temp] values(3, 'bb')
insert into [temp] values(4, 'aa')
insert into [temp] values(4, 'bb')
go--创建合并处理函数
create function f_str(@FId int)
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+';'+Fname
from [temp]
where FId=@FId
return(stuff(@r,1,1,''))
end
go--调用实现查询
select FId,FName=dbo.f_str(FId)
from [temp]
group by FId
go--删除测试
drop table [temp]
drop function f_str/*--测试结果FId FName
----------- -------------------
1 aa;bb;cc;dd
2 aa;bb;cc
3 aa;bb
4 aa;bb(所影响的行数为 4 行)
--*/
drop table [temp]
create table [temp](FId int,FName varchar(2))
insert into [temp] values(1, 'aa')
insert into [temp] values(1, 'bb')
insert into [temp] values(1, 'cc')
insert into [temp] values(1, 'dd')
insert into [temp] values(2, 'aa')
insert into [temp] values(2, 'bb')
insert into [temp] values(2, 'cc')
insert into [temp] values(3, 'aa')
insert into [temp] values(3, 'bb')
insert into [temp] values(4, 'aa')
insert into [temp] values(4, 'bb')
go--创建合并处理函数
create function f_str(@FId int)
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+';'+Fname
from [temp]
where FId=@FId
return(stuff(@r,1,1,''))
end
go--调用实现查询
select FId,FName=dbo.f_str(FId)
from [temp]
group by FId
go--删除测试
drop table [temp]
drop function f_str/*--测试结果FId FName
----------- -------------------
1 aa;bb;cc;dd
2 aa;bb;cc
3 aa;bb
4 aa;bb(所影响的行数为 4 行)
--*/
--如果一定要从临时表中来处理,那就也用临时表if object_id('tempdb..#temp') is not null
drop table #temp
create table #temp
(FId int,
FName varchar(2))
insert into #temp values(1, 'aa')
insert into #temp values(1, 'bb')
insert into #temp values(1, 'cc')
insert into #temp values(1, 'dd')
insert into #temp values(2, 'aa')
insert into #temp values(2, 'bb')
insert into #temp values(2, 'cc')
insert into #temp values(3, 'aa')
insert into #temp values(3, 'bb')
insert into #temp values(4, 'aa')
insert into #temp values(4, 'bb')
go--用临时表处理
select *,aa=cast('' as varchar(8000)) into #t from #temp order by fid,fnamedeclare @fid int,@r varchar(8000)
update #t set @r=case fid when @fid then @r+';'+fname else fname end
,aa=@r,@fid=fidselect Fid,Fname=max(aa)
from #t
group by fid
go--删除测试
drop table #t,#temp/*--测试结果Fid Fname
----------- -----------------
1 aa;bb;cc;dd
2 aa;bb;cc
3 aa;bb
4 aa;bb(所影响的行数为 4 行)
--*/
无法从函数中访问临时表。
http://expert.csdn.net/Expert/topic/2479/2479536.xml?temp=7.28786E-04