Oracle technology: Using analytic functions for row and column conversions

Often have friends ask the row and column conversion problem, the message board also has such a question.

In fact, the use of analytic functions to deal with is a good way to turn over Tom's book, one of the examples included here. For example, to query the Scott.emp table of users Sal sort information, you can use the following query:

Sql> SELECT Deptno, ename,

2 row_number () over (PARTITION by deptno Order by Sal DESC) seq

3 from EMP;

DEPTNO ename SEQ

---------- ---------- ----------

Ten KING 1

Ten CLARK 2

Ten MILLER 3

SCOTT 1

FORD 2

JONES 3

ADAMS 4

SMITH 5

1 BLAKE

ALLEN 2

TURNER 3

WARD 4

MARTIN 5

JAMES 6

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

Rows selected.

Then combine the other functions to do the row and column conversions:

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, sal desc) seq

9 from EMP)

where SEQ <=3 GROUP by Deptno

11/

DEPTNO highest SECOND Third

---------- ---------- ---------- ----------

KING CLARK MILLER

SCOTT FORD JONES

BLAKE ALLEN Turner The result is basically a passable one.

Author: 51cto Blog Oracle Little Bastard