程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle實現行列轉換的方法分析,oracle實現行列轉換

Oracle實現行列轉換的方法分析,oracle實現行列轉換

編輯:Oracle教程

Oracle實現行列轉換的方法分析,oracle實現行列轉換


本文實例講述了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數據庫程序設計有所幫助。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved