csummers.org

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

Hilarious! And for $83 you can have one (in Japan).

Click for story

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;

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…

Like a good son, I bought my dad a Father's Day present from Amazon.com last week. A couple days after ordering I received the shipment confirmation email and proceded to login to my account to track the package–except there's no record of this order in the "Where's My Stuff?" section. It's gone. A previous order shows up from several months ago, but not my most recent one. So, I chalk it up to a backend system error, and figure I'll come back later and maybe it will be there.

I login today and the order is still not showing up. Funny thing is, though, using the tracking number from the shipment confirmation email, I can track the package from the USPS site–just not from within my Amazon account. Oh great–this means I have to contact customer support.

A few canned-response customer support emails later and I'm completely dumbfounded. Apparently I have two Amazon accounts with orders in each. The real kicker, though, is that both accounts have the exact same login email address. The only thing that distinguishes the two accounts is the password! I'm not sure how this happened or why Amazon even allows it to happen, but it's terribly frustrating. And, they will not merge the two accounts into one because of "security concerns." Argh!

My interests in music, guitars, and technology collide together on the computer. I'm also a big fan of free software, so this program is tops on my list for great free software.

Audacity is a free multi-track sound editor that features recording during playback, a host of built-in tools, and a long list of audio effects.

I currently use Audacity to record my church's sermon tapes, clean them up, and then export them to MP3 for download from the website. I've also recently used it to record guitar tracks that were later imported onto a hard disk recorder for vocal tracks to be added.

Audacity is available for Windows, Linux, and Mac. Check it out, and read the help to get started.

A couple weeks ago I received a Dell 20.5" flat panel at work. It's a montrous beauty with 1600×1200 resolution.

Now, I'm a young dude, but default fonts in Windows XP at 1600×1200 resolution are tiny. On a CRT monitor, you can just change to a lower resolution to resolve this problem, but LCDs look best on their intended resolution. So, what to do? Below are some tips that helped me:

Use Larger Fonts:

1. Right-click on the desktop and choose Properties
2. On the Settings tab, click Advanced
3. Under the Display section, choose a larger font DPI. I found the Large Size (120 DPI) to be too large, so I chose custom and settled on 115% (which is 110 DPI)

Fix up scraggly icons in the Quick Launch bar

I use the Quick Launch bar to hold several (18) shortcuts to my most frequently used programs. (My taskbar is set to double-width, so I have 9 icons on each row.) When I changed to the higher resolution for my new LCD, the icons in the Quick Launch bar looked terrible. The edges were jagged and pixelated–very distracting. You can fix this like so:

1. Right-click on the desktop and choose Properties
2. On the Appearance tab, click Advanced
3. In the Item drop-down box choose "Active Title Bar"
4. Adjust the Size value. You may need to experiment with different sizes to get the desired results.

I also adjusted my text editor to slightly larger fonts and my tweaking was complete.

One problem though: After increasing the size of the Active Title Bar, the Quick Launch icons look better, but the icons in the system tray now look jagged. Anyone have a solution for this? I think it may be program specific because not every system tray icon looks bad.