Running Concatenation of column values in oracle sql

Question

I have table structure like below

create table emp ( empno number,dept_no varchar(4));
insert into emp VALUES  (1,'A');
insert into emp  VALUES (1,'B');
insert into emp  VALUES (1,'C');
insert into emp  VALUES (1,'D');
insert into emp  VALUES (2,'P');
insert into emp  VALUES (2,'Q');
insert into emp  VALUES (2,'R');
insert into emp  VALUES (2,'S');

I need output like below

1, A
1, A,B
1, A,B,C
1, A,B,C,D
2, P
2, P,Q
2, P,Q,R
2, P,Q,R,S

I am able to achieve this using PL/SQL Block, however is there any possibility we can achieve this through sql?

Answer 1

This is a task for listagg(). Unfortunately, although it can be used as window function, it does not support the order by window option, which is needed here.

One way to work around this would be to use a correlated subquery:

select 
    e.empno,
    (
            select listagg(e1.dept_no, ',') within group(order by e1.dept_no)
            from emp e1
            where e1.empno = e.empno and e1.dept_no <= e.dept_no            
    ) depts
from emp e

Demo on DB Fiddlde:

EMPNO | DEPTS  
----: | :------
    1 | A      
    1 | A,B    
    1 | A,B,C  
    1 | A,B,C,D
    2 | P      
    2 | P,Q    
    2 | P,Q,R  
    2 | P,Q,R,S

Answer 2

Apart from good answer from @gmb, you can also try following option where table will be traverse only once.

SELECT empno,
    LTRIM(SYS_CONNECT_BY_PATH(dept_no,','),',') AS employees
FROM   (SELECT dept_no,
               empno,
               ROW_NUMBER() OVER (PARTITION BY empno ORDER BY dept_no) AS curr,
               ROW_NUMBER() OVER (PARTITION BY empno ORDER BY dept_no) -1 AS prev
        FROM   emp)
CONNECT BY prev = PRIOR curr
  AND empno = PRIOR empno
START WITH curr = 1;

Db<>fiddle demo

Cheers!!