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

Combine Multiple Columns into single Coma seperated string result.

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

This is a FAQ and is asked here more or less once per week.

One example for a solution is here: Script Library

You can also search for XMLAGG and "string concatenation" to find another solution.

You can use LISTAGG on DB2 9.7 fixpack 4 for LUW.

http://publib.boulder.ibm.com/infoce.../r0058709.html

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

Cast it to a CLOB, may be?

Have found a solution here!

works well

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

Thanks guys