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数据库程序设计有所帮助。