行转列的变种

有群友提出以下问题，如下表所示，每个job显示一行，同类job的姓名分列显示，没有的显示为空。

JOB       N1         N2         N3         N4
--------- ---------- ---------- ---------- ----------
ANALYST   SCOTT      FORD                  
CLERK     SMITH      ADAMS      JAMES      MILLER
MANAGER   JONES      BLAKE      CLARK      
PRESIDENT KING                             
SALESMAN  ALLEN      WARD       MARTIN     TURNER

乍一看，这个语句不好写，因为没有可供case when选用的条件。其实这儿条件是隐藏的，就是各员工姓名的顺序。我们假定按empno排序取得序号即可，则查询语句如下。

SQL> SELECT job,
  2         MAX(CASE
  3               WHEN sn = 1 THEN
  4                ename
  5             END) AS n1,
  6         MAX(CASE
  7               WHEN sn = 2 THEN
  8                ename
  9             END) AS n2,
 10         MAX(CASE
 11               WHEN sn = 3 THEN
 12                ename
 13             END) AS n3,
 14         MAX(CASE
 15               WHEN sn = 4 THEN
 16                ename
 17             END) AS n4
 18    FROM (SELECT job,
 19                 ename,
 20                 row_number() over(PARTITION BY job ORDER BY empno) AS sn
 21            FROM emp)
 22   GROUP BY job;
JOB       N1         N2         N3         N4
--------- ---------- ---------- ---------- ----------
ANALYST   SCOTT      FORD                  
CLERK     SMITH      ADAMS      JAMES      MILLER
MANAGER   JONES      BLAKE      CLARK      
PRESIDENT KING                             
SALESMAN  ALLEN      WARD       MARTIN     TURNER