Recipe14.8.Pivoting a Ranked Result Set

Recipe 14.8. Pivoting a Ranked Result Set

Problem

You want to rank the values in a table, then pivot the result set into three columns. The idea is to show the top three, the next three, then all the rest. For example, you want to rank the employees in table EMP by SAL, and then pivot the results into three columns. The desired result set is as follows:

 TOP_3           NEXT_3          REST --------------- --------------- -------------- KING    (5000)  BLAKE   (2850)  TURNER (1500) FORD    (3000)  CLARK   (2450)  MILLER (1300) SCOTT   (3000)  ALLEN   (1600)  MARTIN (1250) JONES   (2975)                  WARD   (1250)                                 ADAMS  (1100)                                 JAMES  (950)                                 SMITH  (800) 

Solution

The key to this solution is to first use the window function DENSE_RANK OVER to rank the employees by SAL while allowing for ties. By using DENSE_RANK OVER, you can easily see the top three salaries, the next three salaries, and then all the rest.

Next, use the window function ROW_NUMBER OVER to rank each employee within his group (the top three, next three, or last group). From there, simply perform a classic transpose, while using the built-in string functions available on your platform to beautify the results. The following solution uses Oracle syntax. Since both DB2 and SQL Server 2005 support window functions, converting the solution to work for those platforms is trivial:

  1 select max(case grp when 1 then rpad(ename,6) ||  2                     ' ('|| sal ||')' end) top_3,  3       max(case grp when 2 then rpad(ename,6) ||  4                    ' ('|| sal ||')' end) next_3,  5       max(case grp when 3 then rpad(ename,6) ||  6                     ' ('|| sal ||')' end) rest  7   from (  8 select ename,  9        sal, 10        rnk, 11        case when rnk <= 3 then 1 12             when rnk <= 6 then 2 13             else                  3 14        end grp, 15        row_number( )over ( 16          partition by case when rnk <= 3 then 1 17                            when rnk <= 6 then 2 18                            else                 3 19                       end 20              order by sal desc, ename 21        ) grp_rnk 22   from ( 23 select ename, 24        sal, 25        dense_rank( )over(order by sal desc) rnk 26   from emp 27        ) x 28        ) y 29  group by grp_rnk 

Discussion

This recipe is a perfect example of how much you can accomplish with so little, with the help of window functions. The solution may look involved, but as you break it down from inside out you will be surprised how simple it is. Let's begin by executing inline view X first:

  select ename,        sal,        dense_rank( )over(order by sal desc) rnk   from emp ENAME        SAL        RNK ---------- ----- ---------- KING        5000          1 SCOTT       3000          2 FORD        3000          2 JONES       2975          3 BLAKE       2850          4 CLARK       2450          5 ALLEN       1600          6 TURNER      1500          7 MILLER      1300          8 WARD        1250          9 MARTIN      1250          9 ADAMS       1100         10 JAMES        950         11 SMITH        800         12 

As you can see from the result set above, inline view X simply ranks the employees by SAL, while allowing for ties (because the solution uses DENSE_RANK instead of RANK, there are ties without gaps). The next step is to take the rows from inline view X and create groups by using a CASE expression to evaluate the ranking from DENSE_RANK. Additionally, use the window function ROW_NUMBER OVER to rank the employees by SAL within their group (within the group you are creating with the CASE expression). All of this happens in inline view Y and is shown below:

  select ename,        sal,        rnk,        case when rnk <= 3 then 1             when rnk <= 6 then 2             else                  3        end grp,        row_number( )over (          partition by case when rnk <= 3 then 1                            when rnk <= 6 then 2                            else                  3                        end              order by sal desc, ename        ) grp_rnk   from ( select ename,        sal,        dense_rank( )over(order by sal desc) rnk   from emp        ) x ENAME        SAL  RNK  GRP GRP_RNK ---------- ----- ---- ---- ------- KING        5000    1    1       1 FORD        3000    2    1       2 SCOTT       3000    2    1       3 JONES       2975    3    1       4 BLAKE       2850    4    2       1 CLARK       2450    5    2       2 ALLEN       1600    6    2       3 TURNER      1500    7    3       1 MILLER      1300    8    3       2 MARTIN      1250    9    3       3 WARD        1250    9    3       4 ADAMS       1100   10    3       5 JAMES        950   11    3       6 SMITH        800   12    3       7 

Now the query is starting to take shape and, if you followed it from the beginning (from inline view X), you can see that it's not that complicated. The query so far returns each employee, her SAL, her RNK, which represents where her SAL ranks amongst all employees, her GRP, which indicates the group each employee is in (based on SAL), and finally GRP_RANK, which is a ranking (based on SAL) within her GRP.

At this point, perform a traditional pivot on ENAME while using the Oracle concatenation operator || to append the SAL. The function RPAD ensures that the numeric values in parentheses line up nicely. Finally, use GROUP BY on GRP_RNK to ensure you show each employee in the result set. The final result set is shown below:

  select max(case grp when 1 then rpad(ename,6) ||                     ' ('|| sal ||')' end) top_3,        max(case grp when 2 then rpad(ename,6) ||                     ' ('|| sal ||')' end) next_3,        max(case grp when 3 then rpad(ename,6) ||                     ' ('|| sal ||')' end) rest   from ( select ename,        sal,        rnk,        case when rnk <= 3 then 1             when rnk <= 6 then 2             else                   3        end grp,        row_number( )over (          partition by case when rnk <= 3 then 1                            when rnk <= 6 then 2                            else                  3                        end              Order by sal desc, ename        ) grp_rnk   from ( select ename,        sal,        dense_rank( )over(order by sal desc) rnk   from emp        ) x        ) y group by grp_rnk TOP_3           NEXT_3          REST --------------- --------------- ------------- KING    (5000)  BLAKE   (2850)  TURNER (1500) FORD    (3000)  CLARK   (2450)  MILLER (1300) SCOTT   (3000)  ALLEN   (1600)  MARTIN (1250) JONES   (2975)                  WARD   (1250) ADAMS  (1100)                                 JAMES   (950)                                 SMITH   (800) 

If you examine the queries in all of the steps you'll notice that table EMP is accessed exactly once. One of the remarkable things about window functions is how much work you can do in just one pass through your data. No need for self joins or temp tables; just get the rows you need, then let the window functions do the rest. Only in inline view X do you need to access EMP. From there, it's simply a matter of massaging the result set to look the way you want. Consider what all this means for performance if you can create this type of report with a single table access. Pretty cool.