Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table emp ( empno number,dept_no varchar(4));
begin
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');
end;
/
1 rows affected

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


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