Oracle实现行列转换的方法分析_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 不

									……

转换为

这一类型的转换可以借助于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数据库程序设计有所帮助。