Oracle一個典型行列轉換的幾種實現方法

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

SQL> select * from t;

         I A          D
---------- ---------- -------------------
         1 b          2008-03-27 10:55:42
         1 a          2008-03-27 10:55:46
         1 d          2008-03-27 10:55:30
         2 z          2008-03-27 10:55:55
         2 t          2008-03-27 10:55:59

要獲得如下結果，注意字串需要按照D列的時間排序：

1  d,b,a
2  z,t

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

1.自訂函數實現

create or replace function my_concat(n number)
return varchar2
is
 type typ_cursor is ref cursor;
 v_cursor typ_cursor;
 v_temp varchar2(10);
 v_result varchar2(4000):= '';
 v_sql varchar2(200);
begin
 v_sql := 'select a from t where i=' || n ||' order by d';
 open v_cursor for v_sql;
 loop
    fetch v_cursor into v_temp;
    exit when v_cursor%notfound;
    v_result := v_result ||',' || v_temp;
 end loop;
 return substr(v_result,2);
end;

SQL> select i,my_concat(i) from t group by i;

         I MY_CONCAT(I)
---------- --------------------
         1 d,b,a
         2 z,t

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

2.使用sys_connect_by_path

select i,ltrim(max(sys_connect_by_path(a,',')),',') a
from
(
select i,a,d,min(d) over(partition by i) d_min,
(row_number() over(order by i,d))+(dense_rank() over (order by i)) numid
from t
)
start with d=d_min connect by numid-1=prior numid
group by i;

從執行計畫上來看，這種方式只需要掃描兩次表，比自訂函數的方法，效率要高很多，尤其是表中資料量較大的時候：

3.使用wm_sys.wm_concat

這個函數也可以實作類別似的行列轉換需求，但是似乎沒有辦法做到直接根據另外一列排序，所以需要先通過子查詢或者暫存資料表排好序

SQL> select i,wmsys.wm_concat(a) from t group by i;

         I WMSYS.WM_CONCAT(A)
---------- --------------------
         1 b,a,d
         2 z,t

SQL> select i,wmsys.wm_concat(a)
  2  from
  3  (select * from t order by i,d)
  4  group by i;

         I WMSYS.WM_CONCAT(A)
---------- --------------------
         1 d,b,a
         2 z,t

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

不知道大家還有沒有更加高效的實現方式，歡迎指教^_^

http://www.ningoo.net/html/2008/how_to_do_string_aggregate_on_oracle.html

Introduction of WMSYS

WMSSYS is used to store all the metadata information for Oracle Workspace Manager. This user was introduced in Oracle9i and (like most Oracle9i supporting accounts) is locked by default. The user account is locked because we want the password to be public but restrict access to the account to the SYS schema. So, to unlock the account, DBA privileges are required.

This post will show you on how to use the method WMSSYS.WM_CONCAT to convert the row and columns in data table.

SQL> select version from v$instance;
 
VERSION
-----------------
10.2.0.1.0
 
SQL>
SQL> create table IDTABLE
  2  (
  3    id  number,
  4    val varchar2(20)
  5  )
  6  ;
 
Table created
 
SQL>
SQL> insert into IDTABLE (ID, VAL)
  2  values (10, 'abc');
 
1 row inserted
SQL> insert into IDTABLE (ID, VAL)
  2  values (10, 'abc');
 
1 row inserted
SQL> insert into IDTABLE (ID, VAL)
  2  values (10, 'def');
 
1 row inserted
SQL> insert into IDTABLE (ID, VAL)
  2  values (10, 'def');
 
1 row inserted
SQL> insert into IDTABLE (ID, VAL)
  2  values (20, 'ghi');
 
1 row inserted
SQL> insert into IDTABLE (ID, VAL)
  2  values (20, 'jkl');
 
1 row inserted
SQL> insert into IDTABLE (ID, VAL)
  2  values (20, 'mno');
 
1 row inserted
SQL> insert into IDTABLE (ID, VAL)
  2  values (20, 'mno');
 
1 row inserted
 
SQL> select id,val from idtable;
 
        ID VAL
---------- --------------------
        10 abc
        10 abc
        10 def
        10 def
        20 ghi
        20 jkl
        20 mno
        20 mno
 
8 rows selected
 
SQL> commit;
 
Commit complete
 
SQL>
SQL> SELECT ID, WMSYS.WM_CONCAT(VAL) AS ENAMES
  2    FROM IDTABLE
  3   GROUP BY ID;
 
        ID ENAMES
---------- --------------------------------------------------------------------------------
        10 abc,abc,def,def
        20 ghi,jkl,mno,mno
 
SQL>
SQL> SELECT ID, WMSYS.WM_CONCAT(DISTINCT VAL) AS ENAMES
  2    FROM IDTABLE
  3   GROUP BY ID
  4   ORDER BY ID;
 
        ID ENAMES
---------- --------------------------------------------------------------------------------
        10 abc,def
        20 ghi,jkl,mno
 
SQL>
SQL> SELECT ID, VAL, WMSYS.WM_CONCAT(VAL) OVER(PARTITION BY ID) AS ENAMES
  2    FROM IDTABLE
  3   ORDER BY ID;
 
        ID VAL               ENAMES
---------- -------------------- --------------------------------------------------------------------------------
        10 abc                abc,abc,def,def
        10 abc                abc,abc,def,def
        10 def                abc,abc,def,def
        10 def                abc,abc,def,def
        20 ghi                ghi,jkl,mno,mno
        20 jkl                 ghi,jkl,mno,mno
        20 mno               ghi,jkl,mno,mno
        20 mno               ghi,jkl,mno,mno
 
8 rows selected
 
SQL>
SQL> SELECT ID, VAL, WMSYS.WM_CONCAT(VAL) OVER(ORDER BY ID, VAL) AS ENAMES
  2    FROM IDTABLE
  3   ORDER BY ID;
 
        ID VAL               ENAMES
---------- -------------------- --------------------------------------------------------------------------------
        10 abc                abc,abc
        10 abc                abc,abc
        10 def                abc,abc,def,def
        10 def                abc,abc,def,def
        20 ghi                abc,abc,def,def,ghi
        20 jkl                 abc,abc,def,def,ghi,jkl
        20 mno               abc,abc,def,def,ghi,jkl,mno,mno
        20 mno               abc,abc,def,def,ghi,jkl,mno,mno
 
8 rows selected

http://www.cnblogs.com/mikemao/archive/2009/06/11/1501116.html