SQL行列轉換
?
?
??? 在數據庫開發的過程中，行列轉換是一個一定會遇到的問題，解決的方法也有很多，但是對于一些需求復雜，非常制式化的report需求，行列轉換真的是要做到頭大，需要進行模塊化的PLSQL編程來解決。本文摘錄了在Oracle環境下，解決單個字符的行列轉換問題的幾種常用方法，具體如下：
?
??? 首先模擬一下環境，現有的表結構是：
?
create table t8 (i int ,a number ,b varchar2 ( 20 ));
insert into?? t8 values ( 1 , 2 , 'c' );
insert into?? t8 values ( 1 , 3 , 'x' );
insert into?? t8 values ( 1 , 4 , 'b' );
insert into?? t8 values ( 2 , 5 , 'd' );
insert into?? t8 values ( 2 , 6 , 'h' );
insert into?? t8 values ( 3 , 7 , 'j' );
insert into?? t8 values ( 3 , 8 , 'y' );
insert into?? t8 values ( 3 , 9 , 'l' );
insert into?? t8 values ( 3 , 10 , 'v' );
commit;
?
SQL> select * from t8;
?
???????? I????????? A B
---------- ---------- ----------
???????? 1????????? 2 c
???????? 1????????? 3 x
???????? 1????????? 4 b
???????? 2????????? 5 d
???????? 2????????? 6 h
???????? 3????????? 7 j
???????? 3????????? 8 y
???????? 3????????? 9 l
???????? 3???????? 10 v
?
已選擇 9 行。
?
?
??? 希望得到的結果是這樣的：
?
I?????? CHAR
------- ----------
1?????? c,x,b
2?????? d,h
3?????? j,y,l,v
?
?
方法一：
?
??? 如果是 10g ，則可使用最簡單的方法：wm_sys.wm_concat函數 (wm_sys不加也可)
?
SQL> select i,wmsys.wm_concat(b) from t8 group by i;
?
???????? I WMSYS.WM_CONCAT(B)
---------- --------------------
???????? 1 c,x,b
???????? 2 d,h
???????? 3 j,y,l,v
?
??? 缺點：不能加排序功能，例如按 a 列逆序排列，需要在前一層排好序之后再使用wmsys.wm_concat
?
?
方法二：
?
??? 9i 及 9i 以前的數據庫版本可使用 connect by 來實現子葉的連接：
?
select i, ltrim(max (sys_connect_by_path(b, ',' )), ',' ) b
? from (select i,
b,
a,
?????????????? min (a) over( partition by i) a_min,
?????????????? (row_number() over( order by i, a)) +
?????????????? (dense_rank() over( order by i)) numid
????????? from t8)
? start with a = a_min
connect by numid - 1 = prior numid
? group by i;
?
解釋一下這段 SQL ：
?
1、最里面一層，對表 t8 進行處理，主要是列出了列 numid 用于等下的 connect by 操作，具體效果如下：
?
SQL> select i,b,a,min(a) over(partition by i) a_min,
? 2? (row_number() over(order by i, a)) a,
? 3? (dense_rank() over(order by i)) b,
? 4? (row_number() over(order by i, a)) +(dense_rank() over(order by i)) numid
? 5? from t8;
?
?? I B?????? A????? A_MIN??? A??? B????? NUMID
---- ---- ---- ---------- ---- ---- ----------
?? 1 c?????? 2????????? 2??? 1??? 1????????? 2
?? 1 x?????? 3????????? 2??? 2??? 1????????? 3
?? 1 b?????? 4????????? 2??? 3??? 1????????? 4
?? 2 d?????? 5????????? 5??? 4??? 2????????? 6
?? 2 h?????? 6????????? 5??? 5??? 2????????? 7
?? 3 j?????? 7????????? 7??? 6??? 3????????? 9
?? 3 y?????? 8????????? 7??? 7??? 3???????? 10
?? 3 l?????? 9????????? 7??? 8??? 3???????? 11
?? 3 v????? 10????????? 7??? 9??? 3???????? 12
?
9 rows selected
?
其中 row_mun 產生依次列表， dense_rank 產生相同值相同排名，這樣通過 dense_rank 就可以區分出在哪里截斷 connect by
從 numid 列可以看出，每個相同的 i 對應一組序列，之后跳過一個數字后繼續形成序列
?
2、通過 sys_connect_by_path ，以及 start with a = a_min connect by numid - 1 = prior numid 來形成一個分支列表，結果如下：
?
?? I B
---- --------
?? 1 ,c
?? 1 ,c,x
?? 1 ,c,x,b
?? 2 ,d
?? 2 ,d,h
?? 3 ,j
?? 3 ,j,y
?? 3 ,j,y,l
?? 3 ,j,y,l,v
?
3、通過 max 函數選出我們需要的每個 i 對應的最后一列
?
?? I B
---- --------
?? 1 ,c,x,b
?? 2 ,d,h
?? 3 ,j,y,l,v
?
4、用 ltrim 或 substr 來去掉最前面的 “,”
?
?
方法三：
?
自己寫一個函數來處理：
?
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 b from t8 where i=' || n || ' order by a' ;
? 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 t8 group by i;
?
?? I MY_CONCAT(I)
---- ----------------------
?? 1 c,x,b
?? 2 d,h
?? 3 j,y,l,v
?
?
?