Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2008
    Posts
    10

    Unanswered: Problem in making one Query on DB2 Universal Database Platform

    All,

    We would like to know the following:
    e.g:
    Table abc: having two columns (c1,c2) with following data
    C1 C2
    1 abc
    1 pqr
    1 xyz
    2 ddd
    2 yyy
    3 eee


    We want the output such that it should show us the following data:
    1stColname 2ndColname
    1 abc,pqr,xyz
    2 ddd,yyy
    3 eee



    Any suggestions for making a query which gives the above output.

    Thank you.

    ###############
    Regards
    Baseet Ahmed
    ###############

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can do it by using recursive query(with Common Table Expression) or by combination of XMLSERIALIZE, XMLAGG, and XMLELEMENT.

    Although this was quetioned repeatedly in the forums,
    I couldn't find the articles.
    It was difficult to find proper keyword to serch, for me.

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by tonkuma
    You can do it by using recursive query(with Common Table Expression).
    See e.g. the threads http://www.dbforums.com/showthread.php?p=6248263, http://www.dbforums.com/showthread.php?t=918402 and http://www.dbforums.com/showthread.php?t=1200060
    Note that you need to specify the concatenation order in some way or the other, e.g. through a third column (or in the example it's alphabetic on the second column).
    (Aggregate SUM, COUNT etc. are order-indifferent, CONCAT isn't, that's why there is no aggregate CONCAT function.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can do it ..... or by combination of XMLSERIALIZE, XMLAGG, and XMLELEMENT.
    You might want to see these articles.
    "Concatenating rows ?" (in comp.databases.ibm-db2)
    http://groups.google.com/group/comp....f676c8737b6713
    or
    "concatenating historical records Options"
    http://groups.google.com/group/comp....53109d75f4b89b

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by tonkuma
    ... or by combination of XMLSERIALIZE, XMLAGG, and XMLELEMENT.
    I'm always a bit reluctant in directing people to this solution for the concatenation problem.
    If one is familiar with XML, no problem of course. But as you can see from some of the posts in your second pointer article, one gets easily lost in "XML woods" (tags, XML datatype, converting to/from CLOB, ...).
    The "recursive SQL" solution has similar drawbacks, I agree. One has to learn the "new" CTE syntax ("WITH" clause) and on top of that grasp the "recursive SQL" reasoning.

    But here's my point: CTEs and recursive SQL
    (1) are useful in many more SQL-related problem settings; and
    (2) are standard SQL, i.e., your SQL query will be portable to/from other relational platforms.
    --_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
  •