Create A Delimitted String From A Query In Db2

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 i

Solution 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:

createtable t1 (num int, color varchar(10));

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

select num,
  substr( xmlserialize( xmlagg( xmltext( concat( ', ', color ) ) ) asvarchar( 1024 ) ), 3 )
  from t1
  groupby num;

This would return

1 red,black
2 red,yellow,green

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

Solution 2:

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                              
        (SELECTVARCHAR('', 32000), 0, count(description) FROM cte1
         UNIONALLSELECT-- No comma before the first descriptioncasewhen 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 +1AND cte2.cnt < cte2.cnt_max ),                             

    cte3 as                                                          
        (select list from cte2 
         where cte2.cnt = cte2.cnt_max fetchfirst1rowonly)

select list from cte3;

Solution 3:

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 4:

If you are running DB2 9.7 or higher, you can use LISTAGG function. Have a look here: http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0058709.html