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