欢迎来到福编程网,本站提供各种互联网专业知识!
您的位置:网站首页 > 数据库 > Oracle

Oracle实现行列转换的方法分析

发布时间:2016-08-22 作者:sir 来源:转载
这篇文章主要介绍了Oracle实现行列转换的方法,结合实例形式分析了Oracle针对固定列、不定列、列数不固定等情况下的行列转换操作技巧,需要的朋友可以参考下

本文实例讲述了Oracle实现行列转换的方法。分享给大家供大家参考,具体如下:

1、固定列数的行列转换

如:

代码
  1. student subject grade
  2. --------- ---------- --------
  3. student1 语文 80
  4. student1 数学 70
  5. student1 英语 60
  6. student2 语文 90
  7. student2 数学 80
  8. student2 英语 100
  9. ……

转换为:

代码
  1. 语文 数学 英语
  2. student1 80 70 60
  3. student2 90 80 100
  4. ……

语句如下:

代码
  1. select student,
  2. sum(decode(subject,'语文', grade,null)) "语文",
  3. sum(decode(subject,'数学', grade,null)) "数学",
  4. sum(decode(subject,'英语', grade,null)) "英语"
  5. from table
  6. group by student;

2、不定列行列转换

如:

代码
  1. c1 c2
  2. --- -----------
  3. 1 我
  4. 1 是
  5. 1 谁
  6. 2 知
  7. 2 道
  8. 3 不
  9. ……

转换为

代码
  1. 1 我是谁
  2. 2 知道
  3. 3 不

这一类型的转换可以借助于PL/SQL来完成,这里给一个例子

代码
  1. CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)
  2. RETURN VARCHAR2
  3. IS
  4. Col_c2 VARCHAR2(4000);
  5. BEGIN
  6. FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP
  7. Col_c2 := Col_c2||cur.c2;
  8. END LOOP;
  9. Col_c2 := rtrim(Col_c2,1);
  10. RETURN Col_c2;
  11. END;
  12. select distinct c1 ,get_c2(c1) cc2 from table;

或者不用pl/sql,利用分析函数和 CONNECT_BY 实现:

代码
  1. SELECT c1, SUBSTR (MAX (SYS_CONNECT_BY_PATH (c2, ';')), 2) NAME
  2. FROM (SELECT c1, c2, rn, LEAD (rn) OVER (PARTITION BY c1 ORDER BY rn) rn1
  3. FROM (SELECT c1, c2, ROW_NUMBER () OVER (ORDER BY c2) rn
  4. FROM t))
  5. START WITH rn1 IS NULL
  6. CONNECT BY rn1 = PRIOR rn
  7. GROUP BY c1;

3、列数不固定(交叉表行列转置)

这种是比较麻烦的一种,需要借助pl/sql:

原始数据:

代码
  1. CLASS1 CALLDATE CALLCOUNT
  2. 1 2005-08-08 40
  3. 1 2005-08-07 6
  4. 2 2005-08-08 77
  5. 3 2005-08-09 33
  6. 3 2005-08-08 9
  7. 3 2005-08-07 21

转置后:

代码
  1. CALLDATE CallCount1 CallCount2 CallCount3
  2. ------------ ---------- ---------- ----------
  3. 2005-08-09 0 0 33
  4. 2005-08-08 40 77 9
  5. 2005-08-07 6 0 21

试验如下:

1). 建立测试表和数据

代码
  1. CREATE TABLE t(
  2. class1 VARCHAR2(2 BYTE),
  3. calldate DATE,
  4. callcount INTEGER
  5. );
  6. INSERT INTO t(class1, calldate, callcount)
  7. VALUES ('1', TO_DATE ('08/08/2005', 'MM/DD/YYYY'), 40);
  8. INSERT INTO t(class1, calldate, callcount)
  9. VALUES ('1', TO_DATE ('08/07/2005', 'MM/DD/YYYY'), 6);
  10. INSERT INTO t(class1, calldate, callcount)
  11. VALUES ('2', TO_DATE ('08/08/2005', 'MM/DD/YYYY'), 77);
  12. INSERT INTO t(class1, calldate, callcount)
  13. VALUES ('3', TO_DATE ('08/09/2005', 'MM/DD/YYYY'), 33);
  14. INSERT INTO t(class1, calldate, callcount)
  15. VALUES ('3', TO_DATE ('08/08/2005', 'MM/DD/YYYY'), 9);
  16. INSERT INTO t(class1, calldate, callcount)
  17. VALUES ('3', TO_DATE ('08/07/2005', 'MM/DD/YYYY'), 21);
  18. COMMIT ;

2). 建立ref cursor准备输出结果集

代码
  1. CREATE OR REPLACE PACKAGE pkg_getrecord
  2. IS
  3. TYPE myrctype IS REF CURSOR;
  4. END pkg_getrecord;

3). 建立动态sql交叉表函数,输出结果集

代码
  1. CREATE OR REPLACE FUNCTION fn_rs
  2. RETURN pkg_getrecord.myrctype
  3. IS
  4. s VARCHAR2 (4000);
  5. CURSOR c1 IS
  6. SELECT ',sum(case when Class1='
  7. || class1
  8. || ' then CallCount else 0 end)'
  9. || ' "CallCount'
  10. || class1
  11. || '"' c2
  12. FROM t
  13. GROUP BY class1;
  14. r1 c1%ROWTYPE;
  15. list_cursor pkg_getrecord.myrctype;
  16. BEGIN
  17. s := 'select CallDate ';
  18. OPEN c1;
  19. LOOP
  20. FETCH c1 INTO r1;
  21. EXIT WHEN c1%NOTFOUND;
  22. s := s || r1.c2;
  23. END LOOP;
  24. CLOSE c1;
  25. s := s || ' from T group by CallDate order by CallDate desc ';
  26. OPEN list_cursor FOR s;
  27. RETURN list_cursor;
  28. END fn_rs;

4). 测试在sql plus下执行:

代码
  1. var results refcursor;
  2. exec :results := fn_rs;
  3. print results;
  4. CALLDATE CallCount1 CallCount2 CallCount3
  5. --------------- ---------- ---------- ----------
  6. 2005-08-09 0 0 33
  7. 2005-08-08 40 77 9
  8. 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数据库程序设计有所帮助。

相关推荐