参考网址:http://m.aspzz.cn/article/90982.htm https://www.bbsmax.com/A/Vx5MOwGzNr/ https://segmentfault.com/q/1010000009622091/a-1020000009656991 http://blog.itpub.net/17203031/viewspace-754807/
1、固定列数的行列转换
1)
如:
student |
subject |
grade |
|
student1
语文
80
student1
数学
70
student1
英语
60
student2
语文
90
student2
数学
80
student2
英语
100
转换为:
语文 |
数学 |
英语 |
student1
80
70
student2
90
80
语句如下:
select student,sum(decode(subject,'语文',grade,null)) "语文",'数学',null)) "数学",'英语',null)) "英语" from table group by student;
2)
select student,wm_concat(grade) grades from table group by student;
结果
student |
grades |
student1
80 70 60
student2
90 80 100
3)
select student,sum(case when subject='语文' then grade end) "语文",sum(case when subject='数学' then grade end) "数学",sum(case when subject='英语' then grade end) "英语" from table group by student
语文 |
数学 |
英语 |
student1
80
70
student2
90
80
4) 这种办法只适用于oracle 11g及以上版本
select * from table pivot ( count(grade) --可以是其他的聚合函数 for subject in ('语文','英语') );
注意:另有unpivot
2、不定列行列转换
如:
c1 |
c2 |
1
我
1
是
1
谁
2
知
2
道
3
不
转换为
c1 |
c2 |
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,ROW_NUMBER () OVER (ORDER BY c2) rn FROM t)) START WITH rn1 IS NULL CONNECT BY rn1 = PRIOR rn GROUP BY c1;
3、列数不固定
select * from table pivot xml( count(grade) --可以是其他的聚合函数 for subject in (select distinct grade from table) );
注意:这里由于不确定grade的方式,所以使用了xml。实际上这个表只有两个列,一个是学生,一个是xml。xml存储了其他所有列。这种方式在读取数据时会有些复杂。
所以也可以使用字符串链接的方式,将grade一个一个的写出来。
4、列数不固定2(交叉表行列转置)(该方法来源网络,未测试)
这种是比较麻烦的一种,需要借助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,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准备输出结果集
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”,达到了取较小值的目的。
(编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!