Databases Reference
In-Depth Information
One very neat trick with the ranking functions is to pivot the result set. Instead of
showing the rows of the result set down the page, have the results go across the
page. In the following example, the ranking function
ROW_NUMBER
is combined with
the
PIVOT
function:
SQL> select *
2 from (select ename
3 , deptno
4 , rn
5 from (select ename
6 , deptno
7 , row_number() over (partition by deptno
8 order by sal desc
9 ) rn
10 from emp
11 )
12 where rn <= 3
13 )
14 pivot (max (ename)
15 for rn in (1,2,3) )
16 /
DEPTNO 1 2 3
---------- ---------- ---------- ----------
10 KING CLARK MILLER
20 SCOTT FORD JONES
30 BLAKE ALLEN TURNER
At irst, the ranking numbers are assigned in lines 7 to 9 in an inline view. Next the
result set is iltered in line 12. The inal step is to use the
PIVOT
function in line 14. In
case you are wondering if you need to hardcode which rankings you want to pivot,
the answer is yes; currently there is no way to do this declaratively.
Stringing it all together
A frequent requirement is for a way to aggregate strings. In older versions of the
Oracle database this could be implemented, but it was less than trivial to do so.
Nowadays the
LISTAGG
function has relieved this burden. Although not strictly an
analytic function, it can be used like one. The following example shows all the names
of employees within the same department as a comma-separated string:
