如何使用分析函数来进行行和列的转换-开发技术

 SQL> SELECT deptno, ename,
2         ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY sal DESC) seq
3    FROM emp; 网管网www_bitscn_com

    DEPTNO ENAME             SEQ
---------- ---------- ----------
        10 KING                1
        10 CLARK               2
        10 MILLER              3
        20 SCOTT               1
        20 FORD                2
        20 JONES               3
        20 ADAMS               4 网管联盟bitsCN@com
        20 SMITH               5
        30 BLAKE               1
        30 ALLEN               2
        30 TURNER              3
        30 WARD                4
        30 MARTIN              5
        30 JAMES               6 网管u家u.bitscn@com

14 rows selected.

网管u家u.bitscn@com

 SQL> select deptno,
  2  max(decode(seq,1,ename,null)) highest,
  3  max(decode(seq,2,ename,null)) second,
  4  max(decode(seq,3,ename,null)) third
  5  from (
  6  select deptno,ename,
  7  row_number() over
  8  (partition by deptno order by sal desc) seq
  9  from emp)
 10  where seq <=3 group by deptno
 11  /

DEPTNO HIGHEST    SECOND     THIRD
---------- ---------- ---------- ----------
10 KING       CLARK      MILLER
20 SCOTT      FORD       JONES
30 BLAKE      ALLEN      TURNER 中国网管论坛bbs.bitsCN.com