Need Elegant SQL query solution (DB2)

If you are on 9.7 FP4 you can use listagg

drop table rec ;

CREATE TABLE rec
(
  snum      INTEGER,        -- sentence number
  wordnum   INTEGER,        -- word number in the sentence
  word      VARCHAR(100)    -- word being saved
);

INSERT INTO rec
VALUES (1, 1, 'This'), (1, 2, 'is'), (1, 3, 'a'), (1, 4, 'fine'), (1, 5, 'example');


WITH rquery (snum, wordnum, sentence)
AS
(
  SELECT base.snum, base.wordnum, base.word
  FROM rec base
  WHERE wordnum = 1

  UNION ALL

  SELECT t1.snum, t1.wordnum, sentence || ' ' || t1.word
  FROM rquery t0, rec t1
  WHERE t0. snum = t1. snum
    AND t0.wordnum + 1 = t1.wordnum
)
SELECT *
FROM rquery
WHERE wordnum = (SELECT max(wordnum) FROM rquery);


 SELECT snum ,
LISTAGG(word,' ')       WITHIN GROUP(ORDER BY wordnum)
 FROM rec
      GROUP BY snum ;

Select allOpen in new window

http://www.justskins.com/forums/concatenate-column-values-from-145863.html

http://bytes.com/topic/db2/answers/432951-combine-column-multiple-rows-into-one-row

Since I don't know DB2 in depth I'm providing only 2 possible theoretical solutions:

1) Define custom aggregation function (e.g. CONCATE_AGGREGATE) which would concatenate the strings and run the following query:
SELECT CONCATENATE_AGGREGATE(TXT)
FROM  table
GROUP BY JOB
ORDER BY JOB, Sequence

I'm not sure whether DB2 supports it, PostgreSQL does.

2) Define a function with cursor returning such table, e.g. (pseudocode follows):
DEFINE CURSOR c1 FOR
SELECT * FROM table;
VARCHAR curr_txt;
INT curr_job;
BEGIN
OPEN c1;
WHILE FETCH(c1) INTO row DO
  IF row.JOB = curr_job THEN
    curr_txt := curr_txt + row.TXT;
  ELSE
    RETURN NEXT (curr_job, curr_txt);
    curr_txt := row.TXT;
    curr_job := row.JOB;
  END IF;
END WHILE
END;

Maybe somebody will complete this with DB2-specific stuff.

In case you don't have the LISTAGG function, the following code works for me:

select *
  from rec

SNUM         WORDNUM   WORD   
   1               1   This   
   1               2   is     
   1               3   a      
   1               4   fine   
   1               5   example
   2               1   Yet    
   2               2   another
   2               3   example
   2               4   blah1  
   2               5   blah2  
   2               6   blah3  

WITH rquery (snum, wordnum, sentence) as (
 SELECT base.snum, base.wordnum, base.word
    FROM rec base
   WHERE wordnum = 1

  UNION ALL

  SELECT t1.snum, t1.wordnum, sentence || ' ' || t1.word
    FROM rquery t0
    join rec t1
      on t0. snum = t1. snum
     AND t0.wordnum + 1 = t1.wordnum
)
select r1.SNUM,
       r1.SENTENCE
  from rquery r1             
 where r1.wordnum = (        
       select max(r2.wordnum)
       from rquery r2         
      where r2.snum = r1.snum)

SNUM   SENTENCE                             
   1   This is a fine example               
   2   Yet another example blah1 blah2 blah3

Select allOpen in new window

HTH,
DaveSlash

Thanks everyone for the quick responses.  I should have mentioned that i am a newbie at this and need a little more hand holding.  If someone would be so kind as to provide me a working example how to accomplish this using my example table that would be great.

My table is FILE110.DCTXT and the field names are JOB, SEQ, TXT

Thanks in advance

WITH rquery (snum, wordnum, sentence)
AS
(
  SELECT base.job, base.seq, base.txt
  FROM FILE110.DCTXT base
  WHERE wordnum = 1

  UNION ALL

  SELECT t1.job, t1.seq, sentence || ' ' || t1.txt
  FROM rquery t0, FILE110.DCTXT t1
  WHERE t0. snum = t1. job
    AND t0.wordnum + 1 = t1.seq
)
SELECT *
FROM rquery
WHERE wordnum = (SELECT max(wordnum) FROM rquery);


 SELECT snum ,
LISTAGG(word,' ')       WITHIN GROUP(ORDER BY wordnum)
 FROM rec
      GROUP BY snum ;

WITH rquery (job, seq, sentence) as (
 SELECT base.job, base.seq, base.text
    FROM FILE110.DCTXT base
   WHERE seq = 1

  UNION ALL

  SELECT t1.job, t1.seq, sentence || ' ' || t1.text
    FROM rquery t0
    join FILE110.DCTXT t1
      on t0. job = t1. job
     AND t0.seq + 1 = t1.seq
)
select r1.job,
       r1.SENTENCE
  from rquery r1             
 where r1.seq = (        
       select max(r2.seq)
       from rquery r2         
      where r2.job = r1.job)

Select allOpen in new window

OK, LISTAGG is not working for me, I do not know what version.

I was able to use and modify your code DaveSlash, however it is only returning the first and second in the sequence, any more than two does not concat.  Here is what i am using:

WITH rquery (snum, wordnum, sentence) as (
 SELECT base.TXJOB, base.TXSEQ, base.TXTXT
    FROM FILE110.DCTXT base
   WHERE TXSEQ = 1

  UNION ALL

  SELECT t1.TXJOB, t1.TXSEQ, sentence || ' ' || t1.TXTXT
    FROM rquery t0
    join FILE110.DCTXT t1
      on t0. snum = t1. TXJOB
     AND t0.wordnum + 1 = t1.TXSEQ
)
select r1.SNUM,
       r1.SENTENCE
  from rquery r1             
 where r1.wordnum = (        
       select max(r2.wordnum)
       from rquery r2         
      where r2.snum = r1.snum)

Select allOpen in new window

Can you see if this one works

WITH rquery (snum, wordnum, sentence)
AS
(
  SELECT base.snum, base.wordnum, base.word
  FROM rec base
  WHERE wordnum = 1

  UNION ALL

  SELECT t1.snum, t1.wordnum, sentence || ' ' || t1.word
  FROM rquery t0, rec t1
  WHERE t0. snum = t1. snum
    AND t0.wordnum + 1 = t1.wordnum
)
SELECT *
FROM rquery
WHERE wordnum = (SELECT max(wordnum) FROM rquery);

Select allOpen in new window

I get the same result as my previous post... it only joins the first two in the sequence

Beautiful... and elegant as requested