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;
[Show Plain Code]

The results:

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…