游标滚动，最后一条fetch两次？该怎么解决

游标滚动，最后一条fetch两次？
现在查询的结果集如下：
departid name
1 alex
1 david
2 susan
2 linda
想把结果集变成如下形式：
departid name
1 alex,david
2 susan,linda

自己使用方法如下：
declare @aa varchar(10),@bb varchar(50),@cc varchar(10),@dd varchar(10)
create table #temp1
(
departid varchar(10),
name varchar(50)
)
declare cur cursor for
select deartid, name from testcur order by deartid
open cur
fetch next from cur into @aa,@bb
while @@FETCH_STATUS=0
begin

fetch next from cur into @cc,@dd

if(@aa=@cc)
begin
select @bb = @bb+','+@dd
end

else
begin
insert into #temp1 values(@aa,@bb)
set @aa = @cc
set @bb = @dd
end

end
insert into #temp1 values(@aa,@bb)

close cur
deallocate cur

select * from #temp1

显示情况如下：
departid name
1 alex,david
2 susan,linda,linda
为什么会多个linda呢？

至于解决方法见：http://topic.csdn.net/u/20120323/17/5669097e-1e45-4c20-aab1-bace2794bb25.html?1159773523

小弟再次发帖只是不明白自己错在哪里了，求指点！！

[解决办法]

SQL code

create table tb(departid int, name varchar(10))insert into tb values(1 ,'alex')insert into tb values(1 ,'david')insert into tb values(2 ,'susan')insert into tb values(2 ,'lindav')godeclare @t table(departid int,name varchar(100))--定义结果集表变量--定义游标并进行合并处理declare my_cursor cursor local forselect departid , name from tbdeclare @id_old int , @id int , @name varchar(10) , @s varchar(100)open my_cursorfetch my_cursor into @id , @nameselect @id_old = @id , @s=''while @@FETCH_STATUS = 0begin    if @id = @id_old       select @s = @s + ',' + cast(@name as varchar)    else      begin        insert @t values(@id_old , stuff(@s,1,1,''))        select @s = ',' + cast(@name as varchar) , @id_old = @id      end    fetch my_cursor into @id , @nameENDinsert @t values(@id_old , stuff(@s,1,1,''))close my_cursordeallocate my_cursorselect * from @tdrop table tb/*departid    name        ----------- ------------1           alex,david2           susan,lindav（所影响的行数为 2 行）*/
[解决办法]
SQL code--&gt; 测试数据：#temp1if object_id('tempdb.dbo.#temp') is not null drop table #temp1create table #temp([departid] int,[name] varchar(20))insert #tempselect 1,'alex' union allselect 1,'david' union allselect 2,'susan' union allselect 2,'linda'create table #temp2(  departid varchar(10),  name varchar(50))declare @aa varchar(10),@bb varchar(50),@cc varchar(10),@dd varchar(10)declare cur cursor forselect [departid], name from #temp order by [departid]open curfetch next from cur into @aa,@bbwhile @@FETCH_STATUS=0begin  fetch next from cur into @cc,@dd  if(@aa=@cc)  begin  select @bb = @bb+','+@dd  print @bb  end  else  begin  insert into #temp2 values(@aa,@bb)  set @aa=@cc  set @bb=@dd  print @aa  print @bb  fetch cur into @cc , @dd  endend  insert into #temp2 values(@aa,@bb)close curdeallocate curalex,david(1 行受影响)2susansusan,linda正如Vidor所言，你逻辑错误

游标滚动最后一条fetch两次？该怎么

热点推荐