String Aggregate Function

5 pts.
AS/400 DB2
I need a report that provides an aggregate of a string column - in this case a concatenation of the values in a comma separated string. There is no such DB2 aggregate function that does this. A stored procedure (or possibly a customer aggregate function?) seems the only likely way to accomplish this and my procedure writing skills are rather basic. Essentially, I need to loop through a cursor and concatenate the values and return the data. Given this data: 'abc123', 'one' 'abc123', 'two' 'def456', 'one' 'def456', 'two' I want to return it as: 'abc123', 'one,two' 'def456', 'one,two' Any suggestions would be much appreciated. Thanks.

Answer Wiki

Thanks. We'll let you know when a new response is added.

If the comma delimited file will contain the same number of fields each time, create a file on the i5 that matches the coma delimited file. Use CPYTOIMPF to move the file from the IFS to the i5. Call it FILEA.

Then create a file with two fields, one to hold the field containing the ‘abc123’ as the key filed and the second field to hold the ‘one, two, three….’ data (DATA_FIELD). Call it FILEB

Then use the pseudo code below.


Chain (key_field) FILEB;




Discuss This Question: 2  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • JLatos
    Yes, DB2 does have a function for aggregation through concatenation, but it's an XML function. This means you need to convert your strings to XML, perform the aggregations, then convert back to non-XML. It looks like this:
    xml2clob( xmlagg(xmlelement(NAME a, recordID)))
    ,'<A>', ''),'</A>', '; '))
    20 pointsBadges:
  • TomLiotta
    Assuming that you have a current release of the OS (and reasonably current DB2 PTF group level), a basic recursive CTE could be used. Some general information can be found in Recursive query optimization. The examples in the Information Center illustrate summing the costs of a series of connecting flights between cities on a route to a destination. The process of summing would be replaced with concatenation if you wanted a series of strings to be put together. It doesn't require XML nor any extenders, so it's available on basic systems back to V5R4. Tom
    125,585 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: