oracle中行列怎么表示,oracle中行列转换

http://www.eygle.com/archives/2006/09/use_any_function.html

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.

再结合其他函数进行一下行列转换:

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