On Sun, May 08, 2005 at 01:55:56PM -0500, Nick Fankhauser wrote: > > Hi- I'm doing a conversion from an older database in which a memo field was > handled by storing one line per record and then displaying the related > records in order. I want to compress all of the lines into a single text > field with one record per memo entry. > > > So for instance, the old database looks like this: > > > memo_id | sequence | memo_text > --------------------------------------- > 666 | 1 | The quick > 666 | 2 | red fox > 666 | 3 | jumped over > 666 | 4 | the lazy brown dog > > > And my goal is to transform each group of lines into a single record that > looks like this: > > > memo_id | memo_text > ---------------------------------------------------------- > 666 | The quick red fox jumped over the lazy brown dog > > > Any thoughts on how to do this via sql? I could write a little program to > do it, but it seems like there must be a pure-SQL solution that I'm not > seeing.
You can create a custom aggregate to do concatenation and group by memo_id. create or replace function text_cat(text, text) returns text called on null input language sql immutable as 'select case when $1 is null then $2 when $2 is null then $1 else $1 || '' '' || $2 end'; create aggregate textcat (basetype = text, sfunc = text_cat, stype = text); create table memos (memo_id int, sequence int, memo_text text); insert into memos values (666, 3, 'jumped over'); insert into memos values (666, 1, 'The quick'); insert into memos values (666, 4, 'the lazy brown dog'); insert into memos values (666, 2, 'red fox'); select memo_id, textcat(memo_text) from (select * from memos order by memo_id, sequence) as foo group by memo_id; The order is not really guaranteed, though if this is a one-shot thing, you may get away with turning off hashed aggregates. -- Alvaro Herrera (<[EMAIL PROTECTED]>) "There was no reply" (Kernel Traffic) ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
