DB2 Concatenate rows

Question

I'm trying to query the iSeries DB2 v6r1m0. I'd like to borrow answers from Concatenate many rows into a single text string?

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
WHERE Name IS NOT NULL

I have attempted to apply it to the query below, but failed. I'm trying to concatenate the comments.

ATOMIC throws an error. It was taken from one answer somewhere. @comments and comments do not work. Section is a valid token. Not sure what that means.

BEGIN ATOMIC
DECLARE @comments varchar(100)
SELECT
    mh.ID
    ,mh.OtherStuff
    ,me.ID
    ,@Comments = COALESCE(@Comments + '<br />' ,") + me.Comment


FROM
    /*insertTblName*/ mh

INNER JOIN 
    /*insertTblName*/ me
    ON 
        me.ID = mh.ID
WHERE
    me.ID = @parameter
END

I'm trying to accomplish this.

ID      Comment
0       Hello
0       World
1       Foo
1       Bar

To....

ID     Comment
0      Hello World
1      Foo Bar

I usually test my SQL statements in System i Navigator before using them in ADO.Net.

Answer 1

Excellent code, JairoFloresS! It worked beautifully for me.

I just slightly modified your code to make it more generic: I dropped the arbitrary column and instead use relative record number to keep track of original records.

It still works the same (at least on V7R1)!

CREATE TABLE QTEMP/EMP 
(
  DEPTNO NUMERIC    NOT NULL WITH DEFAULT,
  ENAME CHAR ( 10)  NOT NULL WITH DEFAULT
);  


insert into emp values (10,'CLARK '),  
                       (10,'KING  '),  
                       (10,'MILLER'),  
                       (20,'SMITH '),  
                       (20,'ADAMS '),  
                       (20,'FORD  '),  
                       (20,'SCOTT '),  
                       (20,'JONES '),  
                       (30,'ALLEN '),  
                       (30,'BLAKE '), 
                       (30,'MARTIN'), 
                       (30,'JAMES '), 
                       (30,'TURNER'), 
                       (30,'WARD  ')  
;


-- Original data:
select * from qtemp/emp  ;


-- Pivoted and grouped data:
with x (deptno, cnt, list, empno, len) as
 (select z.deptno, 
   (select count(*) from emp y 
    where y.deptno=z.deptno 
    group by y.deptno),
  cast(z.ename as varchar(100)),
  rrn(z), 1  
  from emp z 
 union all  
  select x.deptno,
     x.cnt,
     strip(x.list) ||', '|| e.ename,
     rrn(e),
     x.len+1   
  from emp e, x 
  where e.deptno = x.deptno and rrn(e) > x. empno 
 )  
 select deptno, list, len headcount 
 from x 
 where len=cnt 
;

Output produced looks like so:

Original data:

DEPTNO  ENAME
10      CLARK       
10      KING        
10      MILLER      
20      SMITH       
20      ADAMS       
20      FORD        
20      SCOTT       
20      JONES       
30      ALLEN       
30      BLAKE       
30      MARTIN      
30      JAMES       
30      TURNER      
30      WARD        

Pivoted and grouped data:

DEPTNO  LIST                                    HEADCOUNT
10      CLARK, KING, MILLER                             3   
20      SMITH, ADAMS, FORD, SCOTT, JONES                5       
30      ALLEN, BLAKE, MARTIN, JAMES, TURNER, WARD       6   

Answer 2

Try with this example and data to understand the process and let me know if you solve it.

CREATE TABLE QTEMP/EMP (DEPTNO NUMERIC NOT NULL WITH DEFAULT, ENAME
CHAR ( 10) NOT NULL WITH DEFAULT, EMPNO NUMERIC NOT NULL WITH      
DEFAULT);                                                           


insert into emp values (10,'CLARK ',1),  
                       (10,'KING  ',2),  
                       (10,'MILLER',3),  
                       (20,'SMITH ',4),  
                       (20,'ADAMS ',5),  
                       (20,'FORD  ',6),  
                       (20,'SCOTT ',7),  
                       (20,'JONES ',8),  
                       (30,'ALLEN ',9),  
                       (30,'BLAKE ',10), 
                       (30,'MARTIN',11), 
                       (30,'JAMES ',12), 
                       (30,'TURNER',13), 
                       (30,'WARD  ',14)  

with x (deptno, cnt, list, empno, len)                             
 as (                                                               
select z.deptno, (select count(*) from emp y where y.deptno=z.deptno                                                   group by y.deptno)   
, cast(ename as varchar(100)), empno, 1                             
 from emp z                                                         
 union all                                                          
 select x.deptno, x.cnt, x.list ||' '|| e.ename, e.empno, x.len+1   
 from emp e, x                                                      
 where e.deptno = x.deptno                                          
 and e.empno > x. empno                                             
 )                                                                  
 select deptno, list                                                
 from x                                                             
 where len=cnt

This is the result.

DEPTNO   LIST                                                                        
    10   CLARK      KING       MILLER                                                
    20   SMITH      ADAMS      FORD       SCOTT      JONES                           
    30   ALLEN      BLAKE      MARTIN     JAMES      TURNER     WARD