SQL> select deptno,hiredate,ename from emp order by 1,2;
DEPTNO HIREDATE ENAME
---------- -------------- ----------
10 09-6月 -81 CLARK
10 17-11月-81 KING
10 23-1月 -82 MILLER
20 17-12月-80 SMITH
20 02-4月 -81 JONES
20 03-12月-81 FORD
20 19-4月 -87 SCOTT
20 23-5月 -87 ADAMS
30 20-2月 -81 ALLEN
30 22-2月 -81 WARD
30 01-5月 -81 BLAKE
30 08-9月 -81 TURNER
30 28-9月 -81 MARTIN
30 03-12月-81 JAMES
已选择14行。
SQL> select deptno ,
2 max (ename) ename
3 from
4 ( select deptno,
5 wm_concat(ename) over (partition by deptno order by hiredate) ename
6 from emp )
7 group by deptno;
DEPTNO ENAME
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,JONES,FORD,SCOTT,ADAMS
30 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES
SQL> select wm_concat(ename) as result from (select ename from emp order by ename asc);
RESULT
-----------------------------------------------------------------------------------
ADAMS,ALLEN,BLAKE,CLARK,FORD,JAMES,JONES,KING,MARTIN,MILLER,SCOTT,SMITH,TURNER,WARD