本文实例讲述了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实现行列转换的方法分析的详细内容...