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');
SELECT * FROM rec;
SNUM WORDNUM WORD
1 1 This
1 2 is
1 3 a
1 4 fine
1 5 example
SELECT t0.word ||
COALESCE (' ' || t1.word, '') ||
COALESCE (' ' || t2.word, '') ||
COALESCE (' ' || t3.word, '') ||
COALESCE (' ' || t4.word, '') ||
COALESCE (' ' || t5.word, '') ||
COALESCE (' ' || t6.word, '')
FROM rec t0
LEFT JOIN rec t1
ON t0.snum = t1.snum
AND t1.wordnum = 2
LEFT JOIN rec t2
ON t0.snum = t2.snum
AND t2.wordnum = 3
LEFT JOIN rec t3
ON t0.snum = t3.snum
AND t3.wordnum = 4
LEFT JOIN rec t4
ON t0.snum = t4.snum
AND t4.wordnum = 5
LEFT JOIN rec t5
ON t0.snum = t5.snum
AND t5.wordnum = 6
LEFT JOIN rec t6
ON t0.snum = t6.snum
AND t6.wordnum = 7
WHERE t0.wordnum = 1;
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;
SNUM WORDNUM SENTENCE
1 1 This
1 2 This is
1 3 This is a
1 4 This is a fine
1 5 This is a fine 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);
SNUM WORDNUM SENTENCE
1 5 This is a fine example
INSERT INTO rec
VALUES (2, 1, 'This'), (2, 2, 'is'), (2, 3, 'another'), (2, 4, '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 rq
WHERE rq.wordnum = (SELECT max(wordnum) FROM rquery WHERE snum = rq.snum);
SNUM WORDNUM SENTENCE
1 5 This is a fine example
2 4 This is another example
INSERT INTO rec
VALUES (3, 1, 'This'), (3, 2, 'example '), (3, 3, 'shows'), (3, 4, 'what'),
(3, 5, 'can'), (3, 6, 'happen'), (3, 7, 'when'), (3, 8, 'the'),
(3, 9, 'data'), (3, 10, 'represents'), (3, 11, 'a'), (3, 12, 'really'),
(3, 13, 'long'), (3, 14, 'line'), (3, 15, 'that'), (3, 16, 'overflows'),
(3, 17, 'the'), (3, 18, 'length'), (3, 19, 'of'), (3, 20, 'an'),
(3, 21, 'object');
WITH rquery (snum, wordnum, sentence)
AS
(
SELECT base.snum, base.wordnum, CAST(base.word AS VARCHAR (200))
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 rq
WHERE rq.wordnum = (SELECT max(wordnum) FROM rquery WHERE snum = rq.snum);
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (4)
Commented:
Thank you for putting it together.
Big "Yes" vote above.
Commented:
Commented:
Commented:
A very good article on DB2 recursive query. I also need to write a recursive query (in DB2) and while searching in Google I got this web page. This concept is very new to me. I got a requirement where I need to convert below rows to columns using recursive query..
City_Name State_Name
Montgomery Alabama
Wilmington Delaware
Birmingham Alabama
Anchorage Alaska
Dover Delaware
Query result should be.. (rows will become columns)
Alabama Delaware Alaska
Montgomery
Birmingham
Wilmington
Dover
Anchorage
Just wanted to know your suggestion if this can be achieved using recursive query?
Thanks