Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2008
    Posts
    2

    Unanswered: Concatenate Values from Multiple_Rows into One Column

    db2=8.2 FP9A (32 bit)
    OS = AIX 5.1

    Hi I've seen similar problems to mine floating around the place but i have not been able to adapt their solutions to my problem.

    What i have is a badly designed db with Table1:
    ("NUMBER" CHARACTER(50),
    "REC_NO" INTEGER,
    "DESCRIPTION" CHARACTER(130)
    )

    with data like:
    NUMBER REC_NO DESCRIPTION
    -------------------------------
    1000 1 This change will
    1000 2 make a difference
    1000 3 to the world.
    1001 2 Hello to all
    1001 3 those who care to
    1001 5 read this

    Note that the Rec_no may not start from 1 and may not be consecutive but it is sequential.

    What i need is to to be able to report the table as:
    1000 This change will make a difference to the world
    and
    1001 Hello to all those who care to read this.

    Ideally i would like a function but at the moment getting the SQL correct would be a good start.

    Thanks.

  2. #2
    Join Date
    Jun 2002
    Posts
    15
    Using previous threads as samples, I came up with the following:

    with
    -- create sample table
    temp (number, rec_no, description) as
    ( values ('1000',1,'This change will'), ('1000',2,'make a difference'), ('1000',3,'to the world.')
    ,('1001',2,'Hello to all'), ('1001',3,'those who care to'), ('1001',5,'read this')
    )
    SELECT number,
    VARCHAR(
    REPLACE(
    REPLACE(
    VARCHAR(
    XML2CLOB(
    XMLAGG(
    XMLELEMENT(NAME a, description)
    ORDER BY rec_no)
    ),
    600),
    '<A>', ''),
    '</A>', ' '),
    600) AS transpose_col
    FROM temp
    GROUP BY number
    ;

  3. #3
    Join Date
    Sep 2008
    Posts
    2
    Hi thanks for that but I'm not familiar with the XML expressions... I've never used them before and I'm trying to make sense of the temp table as well.

    Is that meant to be 1 statement?

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Yes, that's just one SQL statement.

    As for the common table expression (it's not a temp table!) and the XML functions, it is best to read it up in the manual. The explanation is typically quite good.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126

    aggregate concatenation

    IMHO, using the XML possibilities of DB2 is overkill, and "unexpected" to say the least. You only want some kind of "aggregate concatenation" (i.e., a concat over the fields of one column in one group), more or less as if you would do aggregate SUM or COUNT.
    Only, concat is order-dependent, while SUM and COUNT aren't; this explains why the SQL syntax doesn't have basic syntax for doing this.

    In the code library of this forum, there is a contribution on aggregate concatenation using CTEs: see http://www.dbforums.com/showthread.p...30#post6323330

    Agreed, this doesn't look simpler than the XML approach, but it "only" needs basic SQL understanding skills, no XML.
    Applied to your example:
    Code:
    WITH t(n, seq, list) AS
    ( SELECT number, rec_no, CAST(RTRIM(description) AS VARCHAR(32000))
      FROM   Table1
     UNION ALL
      SELECT n, rec_no, list || ' ' ||RTRIM(description)
      FROM   t INNER JOIN Table1 ON n = number
      WHERE  LOCATE(RTRIM(description),list) = 0
        AND  rec_no = (SELECT MIN(rec_no) FROM Table1
                       WHERE  number = t.n
                         AND  rec_no > t.seq)
    )
    , l(n, len) AS
    ( SELECT n, MAX(LENGTH(list))
      FROM   t
      GROUP BY n )
    SELECT t.n, t.list
    FROM   t INNER JOIN l  ON  l.n = t.n AND l.len = LENGTH(t.list)
    You may decrease the value "32000", depending on the expected total length of the concatenated "description" column.
    Last edited by Peter.Vanroose; 09-29-08 at 05:27.
    --_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
  •