Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2008
    Posts
    4

    Unanswered: How can IStinging columns in a result

    I have table (CMD) whose data that looks like this
    CALID APP MENU
    70308297 161 161000
    70308297 161 161000
    70308297 161 161100
    70401111 110 110000
    70401111 110 110001
    ...

    I need a query that I can run that will produce This
    70308297 161 161000,161000,161100
    70401111 110 110000,110001
    ...
    Does anybody know who to write this?

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    This is an FAQ. Look for aggregation, concatenation, and "Bill of Material".
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Aug 2008
    Posts
    4

    Where do I fin the FAQ?

    Where do I find the FAQ?

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    There is no collection for FAQs. But your question is very frequently asked. So try google, for example.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Aug 2008
    Posts
    4
    I have found some samples of how to do it in a Stored procedure, I was wondering if it can be done in a single query This is on DB2. any idea? I searched but found nothing

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Yes, use a recursive query. Search in the DB2 LUW manual for "bill of material" as I mentioned above.

    Alternatively, you can use the XMLAGG function to do such an aggregation. First 2 hits on google with search terms "DB2 xmlagg string concatenation" are fine.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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