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:
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
My job requires lots of fun SQL magic. Below is a technique I use frequently to pivot data in SQL.
1) Pivoting values based on a known value
I often need to condense records listed in a table into one record per id by pivoting up some of the values into columns. A good example would be a test score table like so:
ID TEST_CODE TEST_SCORE -- --------- ---------- 1 ACT 21 2 ACT 21 3 SAT 1200 3 ACT 24 4 ACT 19 4 SAT 1010 4 SAT 1100
Since I know the TEST_CODE values that I want to pivot, I can use CASE statements to pivot the values into ACT and SAT columns. Also note that I’m grouping by ID and taking the MAX value of the specified test score. If my table only has one test score per test type, then I could just as well use MIN and get the same result. Otherwise, I’m electing to get the highest score available per person.
SELECT ID, MAX(CASE WHEN TEST_CODE = 'ACT' THEN TEST_SCORE ELSE NULL END) AS ACT_SCORE, MAX(CASE WHEN TEST_CODE = 'SAT' THEN TEST_SCORE ELSE NULL END) AS SAT_SCORE FROM TEST_TABLE GROUP BY ID;
ID ACT_SCORE SAT_SCORE -- --------- --------- 1 21 2 21 3 24 1200 4 19 1100
Pretty simple, right? Ok, but what if you need to pivot data and you don’t know the values that you want to pivot? Or, what if the there are so many possible values to pivot that you can’t afford to give each one it’s own column? I’ll answer that next time…