取出一列之变为两列 decode 用法
> create table stu (name varchar2(10), score number(3),les varchar2(10));
insert into STU (NAME, LES, SCORE) values ('sam', 'math', 40);
insert into STU (NAME, LES, SCORE) values ('tom', 'math', 34);
insert into STU (NAME, LES, SCORE) values ('sam', 'chin', 60);
insert into STU (NAME, LES, SCORE) values ('tom', 'chin', 45);
insert into STU (NAME, LES, SCORE) values ('sam', 'engl', 35);
insert into STU (NAME, LES, SCORE) values ('tom', 'engl', 77);
commit;
Table created.
> select * from stu;
NAME SCORE LES
------------------------------ ---------- ----------
sam 40 math
tom 34 math
sam 60 chin
tom 45 chin
sam 35 engl
tom 77 engl
6 rows selected.
> select name,sum(decode(les,'math',score,null)) math,sum(decode(les,'chin',score,null)) chin,sum(decode(les,'engl',score,null)) engl
2 from stu
3 group by name
4 ;
NAME MATH CHIN ENGL
------------------------------ ---------- ---------- ----------
sam 40 60 35
tom 34 45 77
行列转换实例
表ttt有三个字段
seq --序列
jcxm --检查项目
zhi --值
数据分别如下:
seq jcxm zhi
------- -------- --------
11 1 0.50
11 2 0.21
11 3 0.25
12 1 0.24
12 2 0.30
12 3 0.22
实现功能
创建视图时移动行值为列值
create view v_view1
as
select seq,
sum(decode(jcxm,1, zhi)) 检测项目1,
sum(decode(jcxm,2, zhi)) 检测项目2,
sum(decode(jcxm,3, zhi)) 检测项目3
from ttt
group by seq;
序号 检测项目1 检测项目2 检测项目3
11 0.50 0.21 0.25
12 0.24 0.30 0.22