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

前端技术 2023/09/06 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数据库程序设计有所帮助。

本文地址:https://www.stayed.cn/item/15095

转载请注明出处。

本站部分内容来源于网络,如侵犯到您的权益,请 联系我

我的博客

人生若只如初见,何事秋风悲画扇。