Oracle-casewhen用法-行列转换--688IT编程网

Oracle 行列转换

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

student	subject	grade
student1	语文	80
student1	数学	70
student1	英语	60
Student2	语文	90
Student2	数学	80
Student2	英语	100

转换为

student	语文	数学	英语
student1	80	70	60
Student2	90	80	100

语句如下：

create table tb_chengji (student varchar2(20),subject varchar2(20), grade number);

select student,

sum(case subject when '语文' then grade end) "语文",

sum(case subject when '数学' then grade end) "数学",

sum(case subject when '英语' then grade end) "英语",

sum(grade) "总分"

from tb_chengji

group by student;

或者

select student,

sum(decode(subject,'语文',grade,null)) "语文",

sum(decode(subject,'数学',grade,null)) "数学",

sum(decode(subject,'英语',grade,null)) "英语",

sum(grade) "总分"

from tb_chengji

group by student;　

2、不定列行列转换
如
c1 c2
--- -----------
1 我
1 是

1 谁
2 知
2 道
3 不
……
转换为
1 我是谁
2 知道
3 不

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

create table ttt (c1 number,c2 varchar2(10) );

select * from ttt;

create or replace function get_c2(tmp_c1 number)

return varchar2

col_c2 varchar2(4000);

begin

for cur in (select c2 from ttt 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 ttt

order by c1;
或者不用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 ttt))

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 t22(

class1 VARCHAR2(2 BYTE),

calldate DATE,

callcount INTEGER

);