Oracle横表转纵表

来源：互联网发布：淘宝扣48分有什么影响编辑：程序博客网时间：2024/05/21 04:23

现有scott用户下的emp和dept表

EMP

empno number(4)

ename varchar2(10)

job varchar2(9)

mgr number(4)

hiredate date

sal number(7,2)

comm number(7,2)

deptno number(2)

DEPT

deptno number(2)

dname varchar2(14)

loc varchar2(13)

统计不同部门和工作的员工的总工资

实现横标转换为纵表

decode实现

select d.dname dname,

sum(decode(e.job, 'CLERK', e.sal, 0)) CLERK,

sum(decode(e.job, 'SALESMAN', e.sal, 0)) SALESMAN,

sum(decode(e.job, 'ANALYST', e.sal, 0)) ANALYST,

sum(decode(e.job, 'MANAGER', e.sal, 0)) MANAGER,

sum(decode(e.job, 'PRESIDENT', e.sal, 0)) PRESIDENT

from emp e

join dept d

on e.deptno = d.deptno

group by d.dname;

case when实现

select d.dname dname,

sum(

case e.job

when 'CLERK' then e.sal

else 0

end

) CLERK,

sum(

case e.job

when 'SALESMAN' then e.sal

else 0

end

) SALESMAN,

sum(

case e.job

when 'PRESIDENT' then e.sal

else 0

end

) PRESIDENT,

sum(

case e.job

when 'MANAGER' then e.sal

else 0

end

) MANAGER,

sum(

case e.job

when 'ANALYST' then e.sal

else 0

end

) ANALYST

from emp e

join dept d

on e.deptno = d.deptno

group by d.dname;

带合计项的

select d.dname dname,

sum(decode(e.job, 'CLERK', e.sal, 0)) CLERK,

sum(decode(e.job, 'SALESMAN', e.sal, 0)) SALESMAN,

sum(decode(e.job, 'ANALYST', e.sal, 0)) ANALYST,

sum(decode(e.job, 'MANAGER', e.sal, 0)) MANAGER,

sum(decode(e.job, 'PRESIDENT', e.sal, 0)) PRESIDENT

from emp e

join dept d on e.deptno = d.deptno

group by d.dname

union

select '总计' dname,

sum(decode(e.job, 'CLERK', e.sal, 0)) CLERK,

sum(decode(e.job, 'SALESMAN', e.sal, 0)) SALESMAN,

sum(decode(e.job, 'ANALYST', e.sal, 0)) ANALYST,

sum(decode(e.job, 'MANAGER', e.sal, 0)) MANAGER,

sum(decode(e.job, 'PRESIDENT', e.sal, 0)) PRESIDENT

from emp e

join dept d2 on e.deptno = d2.deptno