DEPTNO ENAME
---------- ------
20 SMITH
10 CELINE
20 BOND
30 ALLEN
30 WARD
20 JONES
30 MARTIN
30 BLAKE
10 CLARK
20 SCOTT
10 KING
30 TURNER
20 ADAMS
30 JAMES
20 FORD
10 MILLER
I want the output like this
DEPTNO ENAME
------ -----
10 CELINE,CLARK,KING,MILLER
20 JONES,ADAMS,BOND,FORD,SCOTT,SMITH
30 MARTIN,JAMES,BLAKE,WARD,TURNER,ALLEN
Hear is the script I am curently working on.
with emp_group as
(
select 20 deptno, 'SMITH' ename from dual union all
select 10, 'CELINE' from dual union all
select 20, 'BOND' from dual union all
select 30, 'ALLEN' from dual union all
select 30, 'WARD' from dual union all
select 20, 'JONES' from dual union all
select 30, 'MARTIN' from dual union all
select 30, 'BLAKE' from dual union all
select 10, 'CLARK' from dual union all
select 20, 'SCOTT' from dual union all
select 10, 'KING' from dual union all
select 30, 'TURNER' from dual union all
select 20, 'ADAMS' from dual union all
select 30, 'JAMES' from dual union all
select 20, 'FORD' from dual union all
select 10, 'MILLER' from dual
)
select
deptno ,
A1
from emp_group
model
ignore nav
partition by (deptno )
dimension by( ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename DESC) rn)
measures( ename , LPAD(' ', 100) A1 )
rules(
A1[rn>0] = ename[cv()],
A1[0] = ename[cv()]
)
order by deptno
;