本文实例讲述了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数据库程序设计有所帮助。