Oracle如何实现动态行列转换,Oracle实现行列转换的方法分析

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

1、固定列数的行列转换

如：

sql;">

student subject grade

--------- ---------- --------

student1 语文 80

student1 数学 70

student1 英语 60

student2 语文 90

student2 数学 80

student2 英语 100

……

转换为：

sql;">

语文 数学 英语

student1 80 70 60

student2 90 80 100

……

语句如下：

sql;">

select student,sum(decode(subject,'语文',grade,null)) "语文",'数学',null)) "数学",'英语',null)) "英语"

from table

group by student;

2、不定列行列转换

如：

sql;">

c1 c2

--- -----------

1 我

1 是

1 谁

2 知

2 道

3 不

……

转换为

sql;">

1 我是谁

2 知道

3 不

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

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 实现：

sql;">

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,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：

原始数据：

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

转置后：

sql;">

CALLDATE CallCount1 CallCount2 CallCount3

------------ ---------- ---------- ----------

2005-08-09 0 0 33

2005-08-08 40 77 9

2005-08-07 6 0 21

试验如下：

1). 建立测试表和数据

sql;">

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,TO_DATE ('08/07/2005',6);

INSERT INTO t(class1,callcount)

VALUES ('2',77);

INSERT INTO t(class1,callcount)

VALUES ('3',TO_DATE ('08/09/2005',33);

INSERT INTO t(class1,9);

INSERT INTO t(class1,21);

COMMIT ;

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

sql;">

CREATE OR REPLACE PACKAGE pkg_getrecord

IS

TYPE myrctype IS REF CURSOR;

END pkg_getrecord;

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

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下执行：

sql;">

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

Value 代表某个表的任何类型的任意列或一个通过计算所得的任何结果。当每个value值被测试，如果value的值为if1，Decode 函数的结果是then1；如果value等于if2，Decode函数结果是then2；等等。事实上，可以给出多个if/then 配对。如果value结果不等于给出的任何配对时，Decode 结果就返回else 。

另外，还可以用decoder函数来比较大小，如下:

sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1

例如：

变量1=10，变量2=20

则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。

更多关于Oracle相关内容感兴趣的读者可查看本站专题：《》、《》及《PHP+Oracle数据库程序设计技巧总结》

希望本文所述对大家Oracle数据库程序设计有所帮助。

总结

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。