SQL to Concat Rows into a list.

Create table emp ( deptno int, ename varchar2(20));

SET DEFINE OFF;
Insert into EMP (DEPTNO, ENAME) Values (10, 'Mike');
Insert into EMP (DEPTNO, ENAME) Values (10, 'Nick');
Insert into EMP (DEPTNO, ENAME) Values (10, 'Carol');
Insert into EMP (DEPTNO, ENAME) Values (20, 'Jack');
Insert into EMP (DEPTNO, ENAME) Values (20, 'Joe');
Insert into EMP (DEPTNO, ENAME) Values (20, 'Janis');
Insert into EMP (DEPTNO, ENAME) Values (20, 'Chris');
Insert into EMP (DEPTNO, ENAME) Values (30, 'Adam');
COMMIT;

Select deptno,substr(max(sys_connect_by_path(ename,';')),2) ename_list
from (
Select deptno, ename, row_number() over(partition by deptno order by ename) r
from emp )
start with r = 1
connect by prior deptno = deptno
and prior r+1 = r
group by deptno

Though the sql looks complex, If you analyze the sql, there is
1) Row_number() which generates a running sequential number per deptno.
2) Connect by Deptno and rownumber
3) Get the max of the list using sys_connect_by_path and group by
4) Substr to remove the first delimiter.