--case when 的行转列select e.job as "工作"，case e.deptno when 10 then sal end as " 部门 10 工资",case e.deptno when 20 then sal end as "部门 20 工资",case e.deptno when 30 then sal end as "部门 30 工资",e.sal as "合计工资"from emp e order by 1--通过工作岗位分组select e.job as "工作"， sum(case e.deptno when 10 then sal end) as " 部门 10 工资",sum(case e.deptno when 20 then sal end) as "部门 20 工资",sum(case e.deptno when 30 then sal end) as "部门 30 工资",sum(e.sal) as "合计工资" from emp egroup by joborder by 1--PVIOTselect * from (select job,sal,deptno from emp)pivot(sum(sal) as sal for deptno in(10 as d10,20 as d20,30 as d30))order by 1--PIVOT只能按一个条件来完成"行转列”，如果同时把工作与部门都转为列，加以汇总时，PIVOT就无能为力了，--只能用case whenselect count(case when deptno = 10 then ename end) as deptno_10,count(case when deptno = 20 then ename end) as deptno_20,count(case when deptno = 30 then ename end) as deptno_30,count(case when job = 'CLERK' then ename end) as clerks,count(case when job = 'MANAGER' then ename end) as MANAGER,count(case when job = 'PRESIDENT' then ename end) as PRESIDENT,count(case when job = 'ANALYST' then ename end) as ANALYST,count(case when job = 'SALESMAN' then ename end) as SALESMAN from emp--列转行 create table test as select * from (select deptno,sal from emp)pivot(count(1) as cnt,sum(sal) as sal for deptno in(10 as deptno_10,20 as deptno_20,30 as deptno_30))order by 1--需求把3个部门的人次显示为一列,比较笨写法。select '10' as "部门编码",deptno_10_cnt as "人次" from testunion allselect '20' as "部门编码",deptno_20_cnt as "人次" from testunion allselect '30' as "部门编码",deptno_30_cnt as "人次" from test--用unpivotselect deptno as 列名,substr(deptno,-6,2) as 部门编码，人次 from test unpivot(人次 for deptno in(deptno_10_cnt,deptno_20_cnt,deptno_30_cnt))--如果要把人次和工资同时转换，得用连接select a.列名,a.部门编码,a.人次,b.工资 from (select substr(deptno,1,9) as 列名,2) as 部门编码，人次 from test unpivot include nulls(人次 for deptno in(deptno_10_cnt,deptno_30_cnt))) ainner join (select substr(deptno,9) as 列名，工资 from test unpivot include nulls(工资 for deptno in(deptno_10_sal,deptno_20_sal,deptno_30_sal))) b on (a.列名 = b.列名)--不用join 还是用unpivotselect deptno as 部门编码,工资,人次 from test unpivot include nulls(人次 for deptno in(deptno_10_cnt as 10,deptno_20_cnt as 20,deptno_30_cnt as 30)) unpivot include nulls(工资 for deptno2 in(deptno_10_sal as 10,deptno_20_sal as 20,deptno_30_sal as 30))where deptno = deptno2--抑制结果集中的重复值select case when lag(e.job) over(order by job,ename) = job then null else job end as 职位,ename as 姓名 from emp ewhere e.deptno = 20order by e.job,e.ename