反向转置结果集

把列转换为行，请看下列结果集：

 

要把它转换为：

 

解决方案:

select dept.deptno,
       case dept.deptno
            when 10 then emp_cnts.deptno_10
            when 20 then emp_cnts.deptno_20
            when 30 then emp_cnts.deptno_30
       end as counts_by_dept
from (
select sum(case when deptno=10 then 1 else 0 end) as deptno_10,
       sum(case when deptno=20 then 1 else 0 end) as deptno_20,
       sum(case when deptno=30 then 1 else 0 end) as deptno_30
from   SCOTT.emp
       )emp_cnts,
       (select deptno from SCOTT.dept where deptno <=30) dept