本文實例講述了Oracle實現行列轉換的方法。分享給大家供大家參考,具體如下:
1、固定列數的行列轉換
如:
student subject grade --------- ---------- -------- student1 語文 80 student1 數學 70 student1 英語 60 student2 語文 90 student2 數學 80 student2 英語 100 ……
轉換為:
語文 數學 英語 student1 80 70 60 student2 90 80 100 ……
語句如下:
select student, sum(decode(subject,'語文', grade,null)) "語文", sum(decode(subject,'數學', grade,null)) "數學", sum(decode(subject,'英語', grade,null)) "英語" from table group by student;
2、不定列行列轉換
如:
c1 c2 --- ----------- 1 我 1 是 1 誰 2 知 2 道 3 不 ……
轉換為
1 我是誰 2 知道 3 不
這一類型的轉換可以借助於PL/SQL來完成,這裡給一個例子
CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER) RETURN VARCHAR2 IS Col_c2 VARCHAR2(4000); BEGIN FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP Col_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 實現:
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 NULL CONNECT BY rn1 = PRIOR rn GROUP BY c1;
3、列數不固定(交叉表行列轉置)
這種是比較麻煩的一種,需要借助pl/sql:
原始數據:
CLASS1 CALLDATE CALLCOUNT 1 2005-08-08 40 1 2005-08-07 6 2 2005-08-08 77 3 2005-08-09 33 3 2005-08-08 9 3 2005-08-07 21
轉置後:
CALLDATE CallCount1 CallCount2 CallCount3 ------------ ---------- ---------- ---------- 2005-08-09 0 0 33 2005-08-08 40 77 9 2005-08-07 6 0 21
試驗如下:
1). 建立測試表和數據
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准備輸出結果集
CREATE OR REPLACE PACKAGE pkg_getrecord IS TYPE myrctype IS REF CURSOR; END pkg_getrecord;
3). 建立動態sql交叉表函數,輸出結果集
CREATE OR REPLACE FUNCTION fn_rs RETURN pkg_getrecord.myrctype IS 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下執行:
var results refcursor; exec :results := fn_rs; print results; CALLDATE CallCount1 CallCount2 CallCount3 --------------- ---------- ---------- ---------- 2005-08-09 0 0 33 2005-08-08 40 77 9 2005-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相關內容感興趣的讀者可查看本站專題:《Oracle常用函數匯總》、《Oracle日期與時間操作技巧總結》及《php+Oracle數據庫程序設計技巧總結》
希望本文所述對大家Oracle數據庫程序設計有所幫助。