Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2011
    Posts
    9

    Unanswered: Combining multiple values into one - Help

    Hey Guys, Junior DB2 DBA here

    An interesting issue im having where i have been asked to return a single string containing all of the codes associated with a specific ID.

    So Data could be (Single table)

    ID Code
    --- -------
    1 ABC
    2 ABF
    3 CDE
    1 DLE
    1 PFS

    And say the request was for ID = 1 the desired result is to have the data look like.

    ID Code
    -- -------
    1 ABC, DLE, PFS

    I have Googled my heart out and all i can find is "Recursive should work" but have yet been able to find an example.

    I have seen a few on these forums specifically this one

    http://www.dbforums.com/db2/1645761-...ng-result.html

    But am having troubles converting this into what im after as i dont quite understand the logic.

    IF anyone could help me out even with a starting point i would be greatful.

    Cheers

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    This is a FAQ and is asked here more or less once per week.

    One example for a solution is here: http://www.dbforums.com/6323330-post12.html

    You can also search for XMLAGG and "string concatenation" to find another solution.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can use LISTAGG on DB2 9.7 fixpack 4 for LUW.

    http://publib.boulder.ibm.com/infoce.../r0058709.html
    Last edited by tonkuma; 11-09-11 at 19:51. Reason: Add reference.

  4. #4
    Join Date
    Jun 2011
    Posts
    9

    9.7.2

    Sadly im not on 9.7.4 and only on FP2.

    I have come up with this below, but need to somehow turn the decleration of "text" into a larger field as it is picking up the limits of the "code" column

    Run SQL

    WITH result(File_id, text) AS (
    SELECT File_id, FL_CODE
    FROM schema.file
    WHERE FILE_ID = '7000'
    UNION ALL
    SELECT c.File_id, r.text ||', '|| FL_CODE
    FROM schema.file c, result r
    )
    SELECT File_id, text from result
    FETCH FIRST 1 ROW ONLY

    Value "XM01, OR05" is too long.. SQLCODE=-433, SQLSTATE=22001, DRIVER=4.11.69

    Failed queries => 1

    Total execution time => 0 ms

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Cast it to a CLOB, may be?

  6. #6
    Join Date
    Jun 2011
    Posts
    9

    Solved!

    Have found a solution here!

    works well

    DB2 Diary from Radhesh: SQL Tips & Techniques- String Aggregation

    Thanks guys

Posting Permissions

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