Create a delimitted string from a query in DB2

Question

I am trying to create a delimitted string from the results of a query in DB2 on the iSeries (AS/400). I've done this in T-SQL, but can't find a way to do it here.

Here is my code in T-SQL. I'm looking for an equivelant in DB2.

DECLARE @a VARCHAR(1000)
SELECT @a = COALESCE(@a + ', ' + [Description], [Description])
FROM AP.Checkbooks
SELECT @a

If the descriptions in my table look like this:

Desc 1
Desc 2
Desc 3

Then it will return this:

Desc 1, Desc 2, Desc 3

Answer 1

Essentially you're looking for the equivalent of MySQL's GROUP_CONCAT aggregate function in DB2. According to one thread I found, you can mimic this behaviour by going through the XMLAGG function:

create table t1 (num int, color varchar(10));

insert into t1 values (1,'red'), (1,'black'), (2,'red'), (2,'yellow'), (2,'green');

select num,
  substr( xmlserialize( xmlagg( xmltext( concat( ', ', color ) ) ) as varchar( 1024 ) ), 3 )
  from t1
  group by num;

This would return

1 red,black
2 red,yellow,green

(or should, if I'm reading things correctly)

Answer 2

I'm trying to do this in OLEDB and from what I understand you can't do this because you can't do anything fancy in SQL for OLEDB like declare variables or create a table. So I guess there is no way.