Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2007
    Posts
    14

    Unanswered: How to aggregate stings in UDB DB2

    I have following sample data
    COL1 COL2
    ----------- ----------
    1 Welcome
    1 udb
    1 database
    2 Toronto
    2 canada

    I want to run a aggreagte function on col2 in such a manner , my select output should be

    1 welcome udb database
    2 toronto canda

    Thx For help

  2. #2
    Join Date
    May 2003
    Posts
    113
    I discussed with my co-work before for similar question, and the conclusion is that there is no way to do this in a single standard sql query(no external UDF or stored proc). And should be fair easy and efficient by using an application. (for example StringBuffer class in java).

    Anyway, with the latest XML functions, it is doable with XMLAGG and XMLQUERY functions, such as(run on db2/zos):


    CREATE TABLE TT
    (COL1 VARCHAR(10));

    INSERT INTO TT VALUES('welcome');
    INSERT INTO TT VALUES('udb');
    INSERT INTO TT VALUES('db2base');

    SELECT
    XMLSERIALIZE(
    XMLQUERY('/fn:data(COL1)' PASSING
    XMLDOCUMENT(
    XMLAGG(XMLELEMENT(NAME "COL1", COL1))) )
    AS CLOB(50) EXCLUDING XMLDECLARATION)
    FROM TT;


    +----------------------------------------------------+
    | |
    +----------------------------------------------------+
    1_| welcome udb db2base |
    +----------------------------------------------------+


    DB2/LUW should support similar functions, but you need V9.

    Anyway, I won't suggest you use above method(which suffer too much overhead when deal with xml).
    Hope someone else has smarter ideas.
    Last edited by nidm; 08-09-07 at 21:05.

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    This can be achieved with recursive SQL using CTEs; 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. (Aggregate SUM, COUNT etc. are order-indifferent, CONCAT isn't.)
    Last edited by Peter.Vanroose; 08-10-07 at 03:03.
    --_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
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by nidm
    I discussed with my co-work before for similar question, and the conclusion is that there is no way to do this in a single standard sql query(no external UDF or stored proc).
    Wrong conclusion.

    Besides the recursive queries that Peter referred to, you can use DB2's XML capabilities and the XMLAGG function. Here is an example:
    http://www.mydatabasesupport.com/for...cte-udf.html#6
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    May 2003
    Posts
    113
    Quote Originally Posted by stolze
    Wrong conclusion.

    Besides the recursive queries that Peter referred to, you can use DB2's XML capabilities and the XMLAGG function. Here is an example:
    http://www.mydatabasesupport.com/for...cte-udf.html#6
    I got confused from your comments. Didn't I provide an xmlagg example in the original comments?

    I don't think standard sql including xml tech. XML is more like another tech(other than sql), but happened to be implemented by IBM inside db2.

Posting Permissions

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