a temp table and a cursor leap to mind...
Dear Downvoters: a temp table and a cursor have got to be at least as efficient as the recursive-query and custom-function solutions accepted above. Get over your fear of cursors, sometimes they are the most efficient solution. Sometimes they are the only solution. Deal with it.
EDIT: cursor-based solution below. Note that it has none of the limitations of the non-cursor (and more complicated) solutions proposed elsewhere, and performance is probably about the same (hard to tell from a six-row table of course).
and please, don't abandon the main for-each construct of sql just because some blogger says "it's bad"; use your own judgement and some common sense. I avoid cursors whenever possible, but not to the point where the solution is not robust.
--initial data table
create table #tmp (
id int,
subid int,
txt varchar(256)
)
--populate with sample data from original question
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')
--temp table for grouping results
create table #tmpgrp (
id int,
txt varchar(4000)
)
--cursor for looping through data
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
--show output
select * from #tmpgrp
--drop temp tables
drop table #tmp
drop table #tmpgrp