不用游标。--1.创建一个合并的函数 create function fmerg(@id int) returns varchar(8000) as begin declare @str varchar(8000) set @str='' select @str=@str+','+f_departname from m_depart where f_messid=@id set @str=right(@str,len(@str)-1) return(@str) End go--调用自定义函数得到结果 select distinct f_messid,dbo.fmerg(id) from m_depart
或:create function getstr(@i varchar(10)) returns varchar(8000) as begin declare @str varchar(8000) set @str='' select @str=@str+','+rtrim(f_departname) from 表 where f_messid=@i set @str=right(@str,len(@str)-1) end--调用: select f_messid1,dbo.getstr(f_messid) f_departname from 表
想用游标当然也行了!declare @temp varchar(100) declare @temp1 varchar(200) DECLARE Employee_Cursor CURSOR FOR SELECT f_departname FROM m_depart where f_messid='01'order by f_messid OPEN Employee_Cursor FETCH NEXT FROM Employee_Cursor into @temp1 WHILE @@FETCH_STATUS = 0 BEGIN set @temp=@temp+@temp1+',' FETCH NEXT FROM Employee_Cursor into @temp1 END CLOSE Employee_Cursor DEALLOCATE Employee_Cursor print @temp
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+f_departname from m_depart where f_messid=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go--调用自定义函数得到结果
select distinct f_messid,dbo.fmerg(id) from m_depart
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+rtrim(f_departname) from 表 where f_messid=@i
set @str=right(@str,len(@str)-1)
end--调用:
select f_messid1,dbo.getstr(f_messid) f_departname from 表
declare @temp1 varchar(200)
DECLARE Employee_Cursor CURSOR FOR
SELECT f_departname FROM m_depart where f_messid='01'order by f_messid
OPEN Employee_Cursor
FETCH NEXT FROM Employee_Cursor into @temp1
WHILE @@FETCH_STATUS = 0
BEGIN
set @temp=@temp+@temp1+','
FETCH NEXT FROM Employee_Cursor into @temp1
END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor
print @temp