我会想到一个临时表和游标...
亲爱的Downvoters:临时表和游标至少与上面接受的递归查询和自定义函数解决方案一样有效。克服你们对游标的恐惧,有时它们是最有效的解决方案。有时它们是唯一的解决方案。接受现实。
编辑:下面是基于游标的解决方案。请注意,它没有其他地方提出的非游标(更复杂)解决方案的任何限制,并且性能可能大致相同(当然从一个六行表中很难判断)。
而且,请不要因为某个博主说“它很糟糕”而放弃SQL的主要for-each结构;要运用自己的判断力和常识。我尽可能避免使用游标,但不至于使解决方案不健壮。
create table #tmp (
id int,
subid int,
txt varchar(256)
)
insert into #tmp (id,subid,txt) values (1, 1, 'Hello')
insert into #tmp (id,subid,txt) values (1, 2, 'World')
insert into #tmp (id,subid,txt) values (1, 3, '!')
insert into #tmp (id,subid,txt) values (2, 1, 'B')
insert into #tmp (id,subid,txt) values (2, 2, 'B')
insert into #tmp (id,subid,txt) values (2, 3, 'Q')
create table #tmpgrp (
id int,
txt varchar(4000)
)
declare cur cursor local for
select id, subid, txt from #tmp order by id, subid
declare @id int
declare @subid int
declare @txt varchar(256)
declare @curid int
declare @curtxt varchar(4000)
open cur
fetch next from cur into @id, @subid, @txt
set @curid = @id
set @curtxt = ''
while @@FETCH_STATUS = 0 begin
if @curid <> @id begin
insert into #tmpgrp (id,txt) values (@curid,@curtxt)
set @curid = @id
set @curtxt = ''
end
set @curtxt = @curtxt + isnull(@txt,'')
fetch next from cur into @id, @subid, @txt
end
insert into #tmpgrp (id,txt) values (@curid,@curtxt)
close cur
deallocate cur
select * from #tmpgrp
drop table #tmp
drop table #tmpgrp