Oracle 多行合并一行 方法

標籤：使用   strong   資料   io   for   art   re   c   

假如有如下表，其中各個i值對應的行數是不定的 

Sql代碼  

1. SQL> select * from t;  
2.   
3.          I A          D  
4. ---------- ---------- -------------------  
5.          1 b          2008-03-27 10:55:42  
6.          1 a          2008-03-27 10:55:46  
7.          1 d          2008-03-27 10:55:30  
8.          2 z          2008-03-27 10:55:55  
9.          2 t          2008-03-27 10:55:59  
10.   
11. --- 要獲得如下結果，注意字串需要按照D列的時間排序：  
12.   
13. 1  d,b,a  
14. 2  z,t  

這是一個比較典型的行列轉換，有好幾種實現方法 

1.自訂函數實現 

Sql代碼  

1. create or replace function my_concat(n number)  
2. return varchar2  
3. is  
4.  type typ_cursor is ref cursor;  
5.  v_cursor typ_cursor;  
6.  v_temp varchar2(10);  
7.  v_result varchar2(4000):= ‘‘;  
8.  v_sql varchar2(200);  
9. begin  
10.  v_sql := ‘select a from t where i=‘ || n ||‘ order by d‘;  
11.  open v_cursor for v_sql;  
12.  loop  
13.     fetch v_cursor into v_temp;  
14.     exit when v_cursor%notfound;  
15.     v_result := v_result ||‘,‘ || v_temp;  
16.  end loop;  
17.  return substr(v_result,2);  
18. end;  
19.   
20. SQL> select i,my_concat(i) from t group by i;  
21.   
22.          I MY_CONCAT(I)  
23. ---------- --------------------  
24.          1 d,b,a  
25.          2 z,t  

雖然這種方式可以實現需求，但是如果表t的資料量很大，i的值又很多的情況下，因為針對每個i值都要執行一句select，掃描和排序的次數和i的值成正比，效能會非常差。 

2.使用sys_connect_by_path 

Sql代碼  

1. select i,ltrim(max(sys_connect_by_path(a,‘,‘)),‘,‘) a  
2. from  
3. (  
4. select i,a,d,min(d) over(partition by i) d_min,  
5. (row_number() over(order by i,d))+(dense_rank() over (order by i)) numid  
6. from t  
7. )  
8. start with d=d_min connect by numid-1=prior numid  
9. group by i;  

從執行計畫上來看，這種方式只需要掃描兩次表，比自訂函數的方法，效率要高很多，尤其是表中資料量較大的時候： 
 
3.使用wm_sys.wm_concat 
這個函數也可以實作類別似的行列轉換需求，但是似乎沒有辦法做到直接根據另外一列排序，所以需要先通過子查詢或者暫存資料表排好序: 

Sql代碼  

1. SQL> select i,wmsys.wm_concat(a) from t group by i;  
2.   
3.          I WMSYS.WM_CONCAT(A)  
4. ---------- --------------------  
5.          1 b,a,d  
6.          2 z,t  
7.   
8. SQL> select i,wmsys.wm_concat(a)  
9.   2  from  
10.   3  (select * from t order by i,d)  
11.   4  group by i;  
12.   
13.          I WMSYS.WM_CONCAT(A)  
14. ---------- --------------------  
15.          1 d,b,a  
16.          2 z,t  

執行計畫上看，只需要做一次表掃描就可以了，但是這個函數是加密過的，執行計畫並不能顯示函數內部的操作。