select d.dname,
listagg (e.job,', ' on overflow truncate with count)
within group (order by e.job) jobs
from scott.dept d, scott.emp e
where d.deptno = e.deptno
group by d.dname
DNAME | JOBS |
---|---|
ACCOUNTING | CLERK, MANAGER, PRESIDENT |
RESEARCH | ANALYST, ANALYST, CLERK, CLERK, MANAGER |
SALES | CLERK, MANAGER, SALESMAN, SALESMAN, SALESMAN, SALESMAN |
SELECT d.dname,
(select LISTAGG(job,', ' ON OVERFLOW TRUNCATE WITH COUNT)
WITHIN GROUP (ORDER BY job)
from (select unique job job
from scott.emp e
where d.deptno = e.deptno)) jobs
FROM scott.dept d
DNAME | JOBS |
---|---|
ACCOUNTING | CLERK, MANAGER, PRESIDENT |
RESEARCH | ANALYST, CLERK, MANAGER |
SALES | CLERK, MANAGER, SALESMAN |
OPERATIONS | - |
select d.dname,
listagg (DISTINCT e.job,', ' on overflow truncate with count)
within group (order by e.job) jobs
from scott.dept d, scott.emp e
where d.deptno = e.deptno
group by d.dname
DNAME | JOBS |
---|---|
ACCOUNTING | CLERK, MANAGER, PRESIDENT |
RESEARCH | ANALYST, CLERK, MANAGER |
SALES | CLERK, MANAGER, SALESMAN |