Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2006
    Posts
    10

    Unanswered: How to make a group

    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
    Last edited by Mastakey; 09-07-06 at 17:06.

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    you can get a better idea from following thread
    http://www.dbforums.com/showthread.php?t=1605979

    --Rahul Singh

  3. #3
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    ID 2
    ----------- --------------------------------------------------------------
    1 abc,ghi,def
    2 maybe,yes,no

  4. #4
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    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

  5. #5
    Join Date
    Sep 2006
    Posts
    10
    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!

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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?
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    May 2006
    Posts
    16
    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.

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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)
    Last edited by Peter.Vanroose; 09-13-06 at 03:29.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •