利用Oracle分析函數實現多行數據合並為一行

　　demo場景以oracle自帶庫中的表emp為例

　　select enamedeptno from emp order by deptno;

　　 ENAME DEPTNO CLARK KING MILLER SMITH ADAMS FORD SCOTT JONES ALLEN BLAKE MARTIN JAMES TURNER WARD

　　現在想要將同一部門的人給合並成一行記錄如何做呢？如下

　　 ENAME DEPTNO CLARKKINGMILLER ADAMSFORDJONESSCOTTSMITH ALLENBLAKEJAMESMARTINTURNERWARD

　　通常我們都是自己寫函數或在程序中處理這裡我們利用oracle自帶的分析函數row_number()和sys_connect_by_path來進行sql語句層面的多行到單行的合並並且效率會非常高

　　基本思路

　　對deptno進行row_number()按ename排位並打上排位號

　　select deptnoenamerow_number() over(partition by deptno order by deptnoename) rank

　　from emp order by deptnoename;

　　 DEPTNO ENAME RANK CLARK KING MILLER ADAMS FORD JONES SCOTT SMITH ALLEN BLAKE JAMES MARTIN TURNER WARD
　　可看出經過row_number()後部門人已經按部門和人名進行了排序並打上了一個位置字段rank

　　利用oracle的遞歸查詢connect by進行表內遞歸並通過sys_connect_by_path進行父子數據追溯串的構造這裡要針對ename字段進行構造使之合並在一個字段內（數據很多只截取部分）

　　select deptnoenameranklevel as curr_level

　　ltrim(sys_connect_by_path(ename)) ename_path from (

　　select deptnoenamerow_number() over(partition by deptno order by deptnoename) rank

　　from emp order by deptnoename) connect by deptno = prior deptno and rank = prior rank;

　　各部門遞歸後的數據量都是(+n)/ * n 即deptno= 數據量(+)/ * = ;

　　deptno= 數據量(+)/ * = ;      deptno= 數據量(+)/ * = ;

　　 DEPTNO ENAME RANK CURR_LEVEL ENAME_PATH CLARK CLARK KING CLARKKING MILLER CLARKKINGMILLER KING KING MILLER KINGMILLER MILLER MILLER

　　 DEPTNO ENAME RANK CURR_LEVEL ENAME_PATH ADAMS ADAMS FORD ADAMSFORD JONES ADAMSFORDJONES SCOTT ADAMSFORDJONESSCOTT SMITH ADAMSFORDJONESSCOTTSMITH FORD FORD JONES FORDJONES SCOTT FORDJONESSCOTT SMITH FORDJONESSCOTTSMITH JONES JONES SCOTT JONESSCOTT SMITH JONESSCOTTSMITH SCOTT SCOTT SMITH SCOTTSMITH SMITH SMITH

　　這裡我們僅列出deptno=的至此我們應該能否發現一些線索了即每個部門中curr_level最高的那行有我們所需要的數據那後面該怎麼辦取出那個數據？ 對了繼續用row_number()進行排位標記然後再按排位標記取出即可

　　 對deptno繼續進行row_number()按curr_level排位

　　select deptnoename_pathrow_number() over(partition by deptno order by deptnocurr_level desc) ename_path_rank from (select deptnoenameranklevel as curr_level

　　ltrim(sys_connect_by_path(ename)) ename_path from (

　　select deptnoenamerow_number() over(partition by deptno order by deptnoename) rank

　　from emp order by deptnoename) connect by deptno = prior deptno and rank = prior rank);

　　 DEPTNO ENAME_PATH ENAME_PATH_RANK CLARKKINGMILLER CLARKKING KINGMILLER CLARK KING MILLER DEPTNO ENAME_PATH ENAME_PATH_RANK ADAMSFORDJONESSCOTTSMITH ADAMSFORDJONESSCOTT FORDJONESSCOTTSMITH ADAMSFORDJONES FORDJONESSCOTT JONESSCOTTSMITH ADAMSFORD FORDJONES SCOTTSMITH JONESSCOTT ADAMS JONES SMITH SCOTT FORD
　　這裡還是僅列出deptno為的至此應該很明了了在進行一次查詢取ename_path_rank為的即可獲得我們想要的結果

　　獲取想要排位的數據即得部門下所有人多行到單行的合並

　　select deptnoename_path from (select deptnoename_path

　　row_number() over(partition by deptno order by deptnocurr_level desc) ename_path_rank

　　from (select deptnoenameranklevel as curr_level

　　ltrim(sys_connect_by_path(ename)) ename_path from (

　　select deptnoenamerow_number() over(partition by deptno order by deptnoename) rank

　　from emp order by deptnoename) connect by deptno = prior deptno and rank = prior rank))

　　where ename_path_rank=;

From:http://tw.wingwit.com/Article/program/Oracle/201311/17343.html