现在查询的结果集如下:
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 curselect * 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小弟再次发帖只是不明白自己错在哪里了,求指点!!
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 curselect * 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小弟再次发帖只是不明白自己错在哪里了,求指点!!
你把你的变量都print 出来看看,看看是在哪儿出问题了
...........
while @@FETCH_STATUS=0
begin
fetch next from cur into @cc,@dd if(@aa=@cc)
begin
select @bb = @bb+','+@dd
print @aa print @bb
end else
begin
insert into #temp1 values(@aa,@bb)
set @aa = @cc
set @bb = @dd
print @aa print @bb
end
end
.............结果如下:1
alex,david(所影响的行数为 1 行)2
mozart
2
mozart,susan
2
mozart,susan,susan(所影响的行数为 1 行)
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 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_cursorselect * from @tdrop table tb/*
departid name
----------- ------------
1 alex,david
2 susan,lindav(所影响的行数为 2 行)
*/
--> 测试数据:#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 curalex,david(1 行受影响)
2
susan
susan,linda正如Vidor所言,你逻辑错误