现在想实现一个字符串累加的问题,表结构如下:
F_ID F_Name
1 'aaaaa'
1 'bbbb'
1 'ccc'
要求想用一条SQL语句实现以下的效果:
F_ID F_Name
1 'aaaaa,bbbb,ccc'
尽量不声明变量,不用临时表,只用SQL语句能不能实现!
--实现
create table tb ([F_ID] int,memo varchar(10))
insert into tb select 1,'aaaaa'
union all select 1,'bbbb'
--(方法一)字符串直接累加
create function FC_StrAdd(@id int)
returns varchar(100)
as
begin
declare @strSql varchar(1000)
set @strSql=''
select @strSql=@strSql+','+cast(F_Name as varchar) from tb where F_ID=@id
return stuff(@strSql,1,1,'')
end
select [F_ID],dbo.FC_StrAdd(F_ID) as F_Name from tb group by F_ID
--(方法二)可以用游标实现
create function FC_StrAdd(@id int)
returns varchar(100)
as
begin
declare @temp varchar(10)
declare @strRet varchar(100)
set @str=''
declare cur cursor for select F_Name from tb where F_ID=@id
open cur
fetch next from cur into @temp
while @@fetch_status=0
begin
set @strRet=@strRet+@temp
fetch next from cur into @temp
end
close cur
deallocate cur
return @strRet
end
select [F_ID],dbo.FC_StrAdd(F_ID) as F_Name from tb group by F_ID