In my last sql post I showed a simple example of pivoting data in SQL when we know the values that we would like to pivot. Often, however, I run into the case where I must pivot data for which:

  1. I don’t know the values I need to pivot, or
  2. there are so many possible values to pivot that I can’t possibly give each its own column

A good example of this is summarizing Financial Aid awards by student for a particular term. The university may have thousands of different awards to give out, but a student usually has no more than 10 awards per term.

So, my source table looks something like this:

TERM    ID    AWARD    AMOUNT
----    --    -----    ------
FA04     1     PELL      4050
FA04     1   ESCHOL       500
FA04     1   WKSTDY      2000
FA04     2     PELL      3500
FA04     2   WKSTDY      2000
FA04     3     PELL      4000

Since I can’t actually pivot the values into their own columns, I’m going to create AWARD columns 1..n containing the first n awards for each student per term. In order to do this, I need to be able to number the awards per student, and then pivot the number. Oracle’s RANK functionality makes this easy:

SELECT
    TERM,
    ID,
    MAX(CASE WHEN RN = 1    THEN AWARD
             ELSE NULL END) AS   AWARD_1,
    MAX(CASE WHEN RN = 1    THEN AMOUNT
             ELSE NULL END) AS   AMOUNT_1,
    MAX(CASE WHEN RN = 2    THEN AWARD
             ELSE NULL END) AS   AWARD_2,
    MAX(CASE WHEN RN = 2    THEN AMOUNT
             ELSE NULL END) AS   AMOUNT_2,
    MAX(CASE WHEN RN = 3    THEN AWARD
             ELSE NULL END) AS   AWARD_3,
    MAX(CASE WHEN RN = 3    THEN AMOUNT
             ELSE NULL END) AS   AMOUNT_3,
FROM
    (SELECT
         RANK() OVER(PARTITION BY
                         TERM,
                         ID
                     ORDER BY
                         TERM,
                         ID,
                         AWARD)  AS RN,
         AWARD,
         AMOUNT
     FROM
         AWARD_TABLE)
GROUP BY
    TERM,
    ID)

And the results:

TERM  ID  AWARD_1  AMOUNT_1  AWARD_2  AMOUNT_2  AWARD_3  AMOUNT_3
----  --  -------  --------  -------  --------  -------  --------
FA04   1   ESCHOL       500     PELL      4050   WKSTDY      2000
FA04   2     PELL      3500   WKSTDY      2000
FA04   3     PELL      4000