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

Solution 1

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.

Solution 2

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)

Solution 3

You can do this using common table expressions (CTEs) and recursion.

with                                                                
    cte1 as                                                             
        (select description, row_number() over() as row_nbr from checkbooks),

    cte2 (list, cnt, cnt_max) AS                              
        (SELECT VARCHAR('', 32000), 0, count(description) FROM cte1
         UNION ALL                                                        
         SELECT 
             -- No comma before the first description
             case when cte2.list = '' THEN RTRIM(CHAR(cte1.description)) 
                  else cte2.list || ', ' || RTRIM(CHAR(cte1.description)) end,   
                  cte2.cnt + 1, 
                  cte2.cnt_max                                
         FROM   cte1,cte2                                                 
         WHERE  cte1.row_nbr = cte2.cnt + 1 AND cte2.cnt < cte2.cnt_max ),                             

    cte3 as                                                          
        (select list from cte2 
         where cte2.cnt = cte2.cnt_max fetch first 1 row only)

select list from cte3;

Comments

[CanSpice]

I'm not sure if this was meant to be a comment to my answer, but my answer doesn't declare variables or create tables. It could be that XMLAGG isn't available to you, though.

[svnvav]

Correct, I guess I don't have XMLAGG, since it doesn't recognize whatever that is in an OLEDB query.

[Zo Has]

He can use a database function

[Funky coder]

+1, side comment: If you are running on Z/OS v10, then change as varchar(1024) to as clob(1024)

[Eric Belair]

I've been looking for this for years. This works for exactly what I need. Thank you.

[ramazan polat]

There should be a +10 button for answers like this.