cookbook 14.9 给两次转置的结果集增加列头

cookbook14.9sql有错误，修改后如下

select a.it_dept as research, b.it_dept as apps
  from (select row_number() over(order by deptno, rn) rn,
               decode(rn, 1, to_char(deptno), ' ' || ename) it_dept
          from (select x.*,
                       y.id,
                       row_number() over(partition by x.deptno order by y.id) rn
                  from (select deptno,
                               ename,
                               count(*) over(partition by deptno) cnt
                          from it_research) x,
                       (select level id from dual connect by level <= 2) y)
         where rn <= cnt + 1) a
  full outer join (select row_number() over(order by deptno, rn) rn,
                          decode(rn, 1, to_char(deptno), ' ' || ename) it_dept
                     from (select x.*,
                                  y.id,
                                  row_number() over(partition by x.deptno order by y.id) rn
                             from (select deptno,
                                          ename,
                                          count(*) over(partition by deptno) cnt
                                     from IT_apps) x,
                                  (select level id
                                     from dual
                                   connect by level <= 2) y)
                    where rn <= cnt + 1) b
    on b.rn = a.rn;

另有解法如下

select a.research, b.apps
  from (select row_number() over(order by deptno, ename nulls first) as sn,
               coalesce(ename, to_char(deptno)) as research
          from (select deptno, null as ename
                  from IT_research
                 group by deptno
                union all
                select deptno, ' ' || ename as ename from IT_research)) a
  full outer join (select row_number() over(order by deptno, ename nulls first) as sn,
                          coalesce(ename, to_char(deptno)) as apps
                     from (select deptno, null as ename
                             from IT_apps
                            group by deptno
                           union all
                           select deptno, ' ' || ename as ename from IT_apps)) b
    on b.sn = a.sn;