Oracle 11g Release 2

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

with depts as (
select 'ACCOUNTING' as department , 'CLERC' as job from dual union all
select 'ACCOUNTING' as department , 'MANAGER' as job from dual union all
select 'ACCOUNTING' as department , 'PRESIDENT' as job from dual union all

select 'RESEARCH' as department , 'ANALYST' as job from dual union all
select 'RESEARCH' as department , 'ANALYST' as job from dual union all
select 'RESEARCH' as department , 'CLERK' as job from dual union all
select 'RESEARCH' as department , 'CLERK' as job from dual union all
select 'RESEARCH' as department , 'MANAGER' as job from dual union all

select 'SALES' as department , 'MANAGER' as job from dual union all
select 'SALES' as department , 'CLERK' as job from dual union all
select 'SALES' as department , 'SALESMAN' as job from dual union all
select 'SALES' as department , 'SALESMAN' as job from dual union all
select 'SALES' as department , 'SALESMAN' as job from dual union all
select 'SALES' as department , 'SALESMAN' as job from dual
)
select department, listagg(job, ',') within group(order by job) jobs
    from (select distinct department, job from depts) t
    group by department

DEPARTMENT	JOBS
ACCOUNTING	CLERC,MANAGER,PRESIDENT
RESEARCH	ANALYST,CLERK,MANAGER
SALES	CLERK,MANAGER,SALESMAN