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')
go

declare @t table(departid int,name varchar(100))--定义结果集表变量
--定义游标并进行合并处理
declare my_cursor cursor local for
select departid , name from tb
declare @id_old int , @id int , @name varchar(10) , @s varchar(100)
open my_cursor
fetch my_cursor into @id , @name
select @id_old = @id , @s=''
while @@FETCH_STATUS = 0
begin
    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 , @name
END
insert @t values(@id_old , stuff(@s,1,1,''))
close my_cursor
deallocate my_cursor

select * from @t

drop table tb

/*
departid    name        
----------- ------------
1           alex,david
2           susan,lindav

（所影响的行数为 2 行）
*/

------解决方案--------------------

SQL code

--> 测试数据：#temp1
if object_id('tempdb.dbo.#temp') is not null drop table #temp1
create table #temp([departid] int,[name] varchar(20))
insert #temp
select 1,'alex' union all
select 1,'david' union all
select 2,'susan' union all
select 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 for
select [departid], name from #temp order by [departid]
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
  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
  end
end
  insert into #temp2 values(@aa,@bb)

close cur
deallocate cur

alex,david

(1 行受影响)
2
susan
susan,linda

正如Vidor所言，你逻辑错误