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

【SQL 分析函式】wm_concat 行列轉換

相關文章