本文實例講述了Oracle實現行列轉換的方法。分享給大家供大家參考，具體如下：
1、固定列數的行列轉換
如：
|
1
2
3
4
5
6
7
8
9
|
student subject grade--------- ---------- --------student1 語文 80student1 數學 70student1 英語 60student2 語文 90student2 數學 80student2 英語 100…… |
轉換為：
|
1
2
3
4
|
語文 數學 英語student1 80 70 60student2 90 80 100…… |
語句如下：
|
1
2
3
4
5
6
|
select student,sum(decode(subject,'語文', grade,null)) "語文",sum(decode(subject,'數學', grade,null)) "數學",sum(decode(subject,'英語', grade,null)) "英語"from tablegroup by student; |
2、不定列行列轉換
如：
|
1
2
3
4
5
6
7
8
9
|
c1 c2--- -----------1 我1 是1 誰2 知2 道3 不…… |
轉換為
|
1
2
3
|
1 我是誰2 知道3 不 |
這一類型的轉換可以借助于PL/SQL來完成，這里給一個例子
|
1
2
3
4
5
6
7
8
9
10
11
12
|
CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)RETURN VARCHAR2ISCol_c2 VARCHAR2(4000);BEGINFOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOPCol_c2 := Col_c2||cur.c2;END LOOP;Col_c2 := rtrim(Col_c2,1);RETURN Col_c2;END;select distinct c1 ,get_c2(c1) cc2 from table; |
或者不用pl/sql，利用分析函數和 CONNECT_BY 實現：
|
1
2
3
4
5
6
7
|
SELECT c1, SUBSTR (MAX (SYS_CONNECT_BY_PATH (c2, ';')), 2) NAME FROM (SELECT c1, c2, rn, LEAD (rn) OVER (PARTITION BY c1 ORDER BY rn) rn1 FROM (SELECT c1, c2, ROW_NUMBER () OVER (ORDER BY c2) rn FROM t))START WITH rn1 IS NULLCONNECT BY rn1 = PRIOR rnGROUP BY c1; |
3、列數不固定（交叉表行列轉置）
這種是比較麻煩的一種，需要借助pl/sql：
原始數據：
|
1
2
3
4
5
6
7
|
CLASS1 CALLDATE CALLCOUNT1 2005-08-08 401 2005-08-07 62 2005-08-08 773 2005-08-09 333 2005-08-08 93 2005-08-07 21 |
轉置后：
|
1
2
3
4
5
|
CALLDATE CallCount1 CallCount2 CallCount3------------ ---------- ---------- ----------2005-08-09 0 0 332005-08-08 40 77 92005-08-07 6 0 21 |
試驗如下：
1）. 建立測試表和數據
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
CREATE TABLE t( class1 VARCHAR2(2 BYTE), calldate DATE, callcount INTEGER);INSERT INTO t(class1, calldate, callcount)VALUES ('1', TO_DATE ('08/08/2005', 'MM/DD/YYYY'), 40);INSERT INTO t(class1, calldate, callcount)VALUES ('1', TO_DATE ('08/07/2005', 'MM/DD/YYYY'), 6);INSERT INTO t(class1, calldate, callcount)VALUES ('2', TO_DATE ('08/08/2005', 'MM/DD/YYYY'), 77);INSERT INTO t(class1, calldate, callcount)VALUES ('3', TO_DATE ('08/09/2005', 'MM/DD/YYYY'), 33);INSERT INTO t(class1, calldate, callcount)VALUES ('3', TO_DATE ('08/08/2005', 'MM/DD/YYYY'), 9);INSERT INTO t(class1, calldate, callcount)VALUES ('3', TO_DATE ('08/07/2005', 'MM/DD/YYYY'), 21);COMMIT ; |
2）. 建立ref cursor準備輸出結果集
|
1
2
3
4
|
CREATE OR REPLACE PACKAGE pkg_getrecordIS TYPE myrctype IS REF CURSOR;END pkg_getrecord; |
3）. 建立動態(tài)sql交叉表函數，輸出結果集
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
CREATE OR REPLACE FUNCTION fn_rs RETURN pkg_getrecord.myrctypeIS s VARCHAR2 (4000); CURSOR c1 IS SELECT ',sum(case when Class1=' || class1 || ' then CallCount else 0 end)' || ' "CallCount' || class1 || '"' c2 FROM t GROUP BY class1; r1 c1%ROWTYPE; list_cursor pkg_getrecord.myrctype;BEGIN s := 'select CallDate '; OPEN c1; LOOP FETCH c1 INTO r1; EXIT WHEN c1%NOTFOUND; s := s || r1.c2; END LOOP; CLOSE c1; s := s || ' from T group by CallDate order by CallDate desc '; OPEN list_cursor FOR s; RETURN list_cursor;END fn_rs; |
4）. 測試在sql plus下執(zhí)行：
|
1
2
3
4
5
6
7
8
|
var results refcursor;exec :results := fn_rs;print results;CALLDATE CallCount1 CallCount2 CallCount3--------------- ---------- ---------- ----------2005-08-09 0 0 332005-08-08 40 77 92005-08-07 6 0 21 |
說明:decode
DECODE(value, if1, then1, if2,then2, if3,then3, . . . else )
Value 代表某個表的任何類型的任意列或一個通過計算所得的任何結果。當每個value值被測試，如果value的值為if1，Decode 函數的結果是then1；如果value等于if2，Decode函數結果是then2；等等。事實上，可以給出多個if/then 配對。如果value結果不等于給出的任何配對時，Decode 結果就返回else 。
另外，還可以用decoder函數來比較大小，如下:
select decode(sign(變量1-變量2),-1,變量1,變量2) from dual; --取較小值
sign()函數根據某個值是0、正數還是負數，分別返回0、1、-1
例如：
變量1=10，變量2=20
則sign(變量1-變量2)返回-1，decode解碼結果為“變量1”，達到了取較小值的目的。
希望本文所述對大家Oracle數據庫程序設計有所幫助。
