行转列
新建表并插入数据:
create table t_grade
(name varchar2(50),
chinese_score number,
english_score number,
math_score number
);
insert into t_grade values ('张三',90,81,80);
insert into t_grade values ('李四',85,90,87);
commit;
select * from t_grade;
1、请把表格转换成下面的格式:
select name,'语文' as subject,chinese_score as result from t_grade union
select name,'英语' as subject,english_score as result from t_grade union
select name,'数学' as subject,math_score as result from t_grade
order by name
以Oracle中scott的emp为例,统计各职位的人员在各部门的人数分布情况,就可以用“行转列”:
使用“行转列”统计各职位的人员在各部门的分布人数后,数据为:
select t.job, count(decode(t.deptno, '10', 1)) as "10(DEPTNO)",
count(decode(t.deptno, '20', 1)) as "20(DEPTNO)",
count(decode(t.deptno, '30', 1)) as "30(DEPTNO)",
count(decode(t.deptno, '40', 1)) as "40(DEPTNO)"
from scott.emp t
group by t.job;