将结果集反向转置为一列

比如说现在有如下结果集

SQL> select ename,job,sal
  2  from emp
  3  where deptno=10;

ENAME      JOB              SAL
---------- --------- ----------
CLARK      MANAGER         2450
KING       PRESIDENT       5000
MILLER     CLERK           1300

 

你现在想要把员工这三个值放到一个列中，为每个员工返回三行信息，而且在两个员工之间加上一个空白行，如下所示

SQL> select case rn
  2  when 1 then ename
  3  when 2 then job
  4  when 3 then cast(sal as char(4))
  5  end emps
  6  from
  7  (
  8  select e.ename,e.job,e.sal,row_number() over(partition by e.empno order by
e.empno) rn
  9  from emp e,
 10  (select rownum from dual connect by rownum<=4) four_rows
 11  where e.deptno=10
 12  )
 13  ;

EMPS
----------
CLARK
MANAGER
2450

 

KING
PRESIDENT
5000

 

MILLER
CLERK
1300

 

12 rows selected.

 

现在我们来小分析一下。由于结果集总共需要12行，所以内层的select构成了一个笛卡尔积，3*4=12行

SQL> select e.ename,e.job,e.sal,row_number() over(partition by e.empno order by
e.empno) rn
  2  from emp e,
  3  (select rownum from dual connect by rownum<=4) four_rows
  4  where e.deptno=10;

ENAME      JOB              SAL         RN
---------- --------- ---------- ----------
CLARK      MANAGER         2450          1
CLARK      MANAGER         2450          2
CLARK      MANAGER         2450          3
CLARK      MANAGER         2450          4
KING       PRESIDENT       5000          1
KING       PRESIDENT       5000          2
KING       PRESIDENT       5000          3
KING       PRESIDENT       5000          4
MILLER     CLERK           1300          1
MILLER     CLERK           1300          2
MILLER     CLERK           1300          3

ENAME      JOB              SAL         RN
---------- --------- ---------- ----------
MILLER     CLERK           1300          4

12 rows selected.

 

这里row_number()的作用是为每个员工生成的四行记录予以区分，以便在外层使用case语句转置的时候起作用。