把结果集转置为多行

emp表中有如下数据

SQL> select job,ename from emp;

JOB       ENAME
--------- ----------
CLERK     SMITH
SALESMAN  ALLEN
SALESMAN  WARD
MANAGER   JONES
SALESMAN  MARTIN
MANAGER   BLAKE
MANAGER   CLARK
ANALYST   SCOTT
PRESIDENT KING
SALESMAN  TURNER
CLERK     ADAMS

JOB       ENAME
--------- ----------
CLERK     JAMES
ANALYST   FORD
CLERK     MILLER

14 rows selected.

 

现在要达到如下效果

SQL> select
  2  max(case when job='CLERK' then ename else null end) as clerks,
  3  max(case when job='ANALYST' then ename else null end) as analyst,
  4  max(case when job='MANAGER' then ename else null end) as manager,
  5  max(case when job='PRESIDENT' then ename else null end) as president,
  6  max(case when job='SALESMAN' then ename else null end) as salesman
  7  from
  8  (
  9  select job,ename,row_number() over(partition by job order by ename) rn
 10  from emp
 11  )
 12  group by rn;

CLERKS     ANALYST    MANAGER    PRESIDENT  SALESMAN
---------- ---------- ---------- ---------- ----------
ADAMS      FORD       BLAKE      KING       ALLEN
JAMES      SCOTT      CLARK                 MARTIN
SMITH                                       WARD
MILLER                JONES                 TURNER

 

可能有人会产生疑问，这里为什么要使用row_number() over()呢？下面我们把内层查询的这一步取掉看看结果

SQL> select
  2  max(case when job='CLERK' then ename else null end) as clerks,
  3  max(case when job='ANALYST' then ename else null end) as analyst,
  4  max(case when job='MANAGER' then ename else null end) as manager,
  5  max(case when job='PRESIDENT' then ename else null end) as president,
  6  max(case when job='SALESMAN' then ename else null end) as salesman
  7  from emp;

CLERKS     ANALYST    MANAGER    PRESIDENT  SALESMAN
---------- ---------- ---------- ---------- ----------
SMITH      SCOTT      JONES      KING       WARD

 

对于一个给定职位，每个ENAME都有唯一的“行号”，这就可以避免可能有两个员工名字和职位都相同而带来的问题。这样做的目的是能够按行号（RN）分组，在使用了MAX时不会遗漏结果集中的任何员工。这是解决本问题最重要的步骤，如果没有这一步，外层查询的聚集运算会丢失掉一些有用的行，如上面的例子所示。

 

SQL> select rn,
  2  case when job='CLERK' then ename else null end as clerks,
  3  case when job='ANALYST' then ename else null end as analyst,
  4  case when job='MANAGER' then ename else null end as manager,
  5  case when job='PRESIDENT' then ename else null end as president,
  6  case when job='SALESMAN' then ename else null end as salesman
  7  from
  8  (
  9  select job,ename,row_number() over(partition by job order by ename) rn
 10  from emp
 11  );

        RN CLERKS     ANALYST    MANAGER    PRESIDENT  SALESMAN
---------- ---------- ---------- ---------- ---------- ----------
         1            FORD
         2            SCOTT
         1 ADAMS
         2 JAMES
         3 MILLER
         4 SMITH
         1                       BLAKE
         2                       CLARK
         3                       JONES
         1                                  KING
         1                                             ALLEN

        RN CLERKS     ANALYST    MANAGER    PRESIDENT  SALESMAN
---------- ---------- ---------- ---------- ---------- ----------
         2                                             MARTIN
         3                                             TURNER
         4                                             WARD

14 rows selected.

上面的结果已经很接近了，再剔除掉NULL值就可以了。这由MAX和GROUP BY来完成。