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

楊奇龍發表於2010-10-22

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  

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22664653/viewspace-676589/,如需轉載,請註明出處,否則將追究法律責任。

相關文章