views:

292

answers:

3
+2  Q: 

Fold in T-SQL?

If I have data in the following format

 id    subid      text
 1     1          Hello
 1     2          World
 1     3          !
 2     1          B
 2     2          B
 2     3          Q

And would like it in this format:

 id  fold
 1   HelloWorld!
 2   BBQ

How could I accomplish it in T-SQL?

+1  A: 

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
Steven A. Lowe
Cursors are bad m'kay.
StingyJack
@[StingyJack]: except when they're the only efficient solution
Steven A. Lowe
@StingyJack: Cursors definitely have their place. However, as all have mentioned, this is not the place.
Jason Lepack
@StingyJack: cursor solution added
Steven A. Lowe
@[Jason Lepack]: cursor solution added
Steven A. Lowe
@AviewAnew: sometimes there's no school like the old school, see edits
Steven A. Lowe
Yes, cursors are bad unless you have no other option. I have _had_ to use them before, but my point was more that they should be avoided like the plague if at all possible. Your example should be declaring a FAST_FORWARD since you are read-only and only moving forward.
StingyJack
@Steve I don't really understand why you directed it at me, I completely believe in choosing the best tool for the job even if people are scared of it (I actually use gotos occasionally). I accepted Jason's because I think it's the right mix of caution and completeness
Tom Ritter
@StingyJack: you are correct, thanks for the tweak!
Steven A. Lowe
@AviewAnew: I directed a comment at you so you would notice another solution, that's all!
Steven A. Lowe
+6  A: 

I would strongly suggest against that. That is the sort of thing that should be handled in your application layer.

But... if you must:
Concatenating row values in Transact-SQL

Jason Lepack
WOW those examples are intense.
StingyJack
I'm not sure what you mean by intense.
Jason Lepack
seemingly very involved and abstracted.
StingyJack
Agreed. However, that's why I suggest against doing it in T-SQL and handling it in the application layer.
Jason Lepack
A: 

Wrap this in a function for a single execution...

DECLARE @returnValue varchar(4000)

SELECT @returnValue = ISNULL(@returnValue + ', ' +  myTable.text, myTable.text)
FROM myTable 

RETURN @returnValue

For a small number of records this will work... any more than 5 or 10 is too many for a SQL function and it needs to be moved to app layer as others have suggested.

StingyJack