如何使用分析函數(shù)來進(jìn)行行和列的轉(zhuǎn)換

使用分析函數(shù)進(jìn)行行轉(zhuǎn)列的處理： 比如查詢scott.emp表的用戶SAL排序信息，大家可以使用下面的查詢：
    SQL> SELECT deptno, ename,
    2 ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY sal DESC) seq
    3 FROM emp;
     DEPTNO ENAME SEQ
    ---------- ---------- ----------
     10 KING 1
     10 CLARK 2
     10 MILLER 3
     20 SCOTT 1
     20 FORD 2
     20 JONES 3
     20 ADAMS 4
     20 SMITH 5
     30 BLAKE 1
     30 ALLEN 2
     30 TURNER 3
     30 WARD 4
     30 MARTIN 5
     30 JAMES 6
    14 rows selected.
    然后你可以結(jié)合其他函數(shù)進(jìn)行行和列的轉(zhuǎn)換:
    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