个人中心
gpt4 book ai didi

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

转载 作者：qq735679552 更新时间：2022-09-29 22:32:09 26 4
gpt4 key购买 nike

CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章Oracle实现行列转换的方法分析由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.

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

1、固定列数的行列转换 。

如:

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

转换为:

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

语句如下:

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

2、不定列行列转换 。

如:

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

转换为 。

?
1
2
3
1 我是谁
2 知道
3 不

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

?
1
2
3
4
5
6
7
8
9
10
11
12
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 实现:

?
1
2
3
4
5
6
7
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:

原始数据:

?
1
2
3
4
5
6
7
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

转置后:

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

试验如下:

1）. 建立测试表和数据 。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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准备输出结果集 。

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

3）. 建立动态sql交叉表函数，输出结果集 。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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下执行:

?
1
2
3
4
5
6
7
8
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实现行列转换的方法分析的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。

26 4 0
文章推荐： 使用JDBC4.0操作Oracle中BLOB类型的数据方法
文章推荐： 连接Oracle数据库时报ORA-12541:TNS:无监听程序的图文解决教程
文章推荐： Oracle客户端与plsql查询数据乱码修改成中文的快速解决方法
文章推荐： Oracle客户端版本及位数（Windows系统）查看方法
qq735679552
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com