Hello, I am new here and I am novice at DB2 and I am having some diffultity with a simple problem.

I have a table like this:
id word
1 abc
1 def
1 ghi
2 one
2 two
2 three
3 yes
3 no
3 maybe

I want to make a view like this using the table above:

id words
1 abc, def, ghi
2 one, two, three
3 yes, no, maybe

Don't neccessarily need the commas there, spaces or any other separator is fine.
I am not sure how I can go about doing this purely in SQL. Is it possible to do this without using cursors or loops? i.e. is there a easy and simple solution to this? Any sugguestions or Help is well appreciated.


Thanks!

Edit: I am running DB2 8.2.1

you can get a better idea from following thread
SQL query

--Rahul Singh

ID 2
----------- --------------------------------------------------------------
1 abc,ghi,def
2 maybe,yes,no

Assuming name of table as test and considering that for 1 id there are only three or less distinct nos.

with temp (id,word) as
(select id, min(word)||','||max(word) con from test group by id)
select a.id,a.word||','||b.word from temp A , test B where a.id=b.id
and locate(b.word,a.word)=0

ID 2
----------- --------------------------------------------------------------
1 abc,ghi,def
2 maybe,yes,no

--Rahul Singh

Hey, thanks for the quick reply!
The suggestion on other thread works as usual but I am more interested in the query in your last post. It works as well, but my DB has much more words than 3. Is there a cheap way to make your query work for more words than 3? I expect each id to have about 20 words!

Thanks again!


Thanks again!

The CTE ("with" expression) allows for recursion: within the first select, which is the definition of table "temp", that table "temp" may be used.

I have the feeling that recursion could give the general solution: recursively concatenating fields with the same id but from different rows, until no rows are left in that group. But I can't come up with a working query yet ...

Somebody else who sees the light?

If you can add a sequence column (like an identity column), the next query could be a solution.

WITH TEMP1 (ID1, WORD1, SEQ1) AS
(SELECT ID, VARCHAR(RTRIM(MIN(WORD)),20), MIN(SEQ)
FROM TBL01 T
GROUP BY ID, SEQ, WORD
UNION ALL
SELECT A.ID, RTRIM(B.WORD1) || RTRIM(A.WORD), A.SEQ
FROM TEMP1 B, TBL01 A
WHERE A.ID = B.ID1
AND A.SEQ = B.SEQ1 + 1)
SELECT ID1, MAX(WORD1)
FROM TEMP1 C
GROUP BY ID1;

Saludos, ARLF.

This will only work if the sequence number (1) has no holes, and (2) does not jump back and forth between groups of identical id.
But you can virtually add such a sequence number by using the OLAP function ROWNUMBER() OVER (ORDER BY id)