Reporting with SQL PIVOT

I wanted to write about another bit of code that I created for the What Color Is...? website I talked about in my previous post. 

Displaying the Results

Probably the most interesting and challenging part of this project was to come up with a way to generate and display the results in an efficient way.  Colors were all saved in the database as 3 separate fields (Red, Green, and Blue).  This made it easy to select a color average for all responses.  Here is the SQL:

SELECT  t.name ,
        COUNT(*) AS Count,
        AVG(IsNull(red,255)) as Red ,
        AVG(IsNull(green,255)) as Green ,
        AVG(IsNull(blue,255)) as Blue
FROM    WCIResponse r
        INNER JOIN WCIThing t ON r.ThingID = t.ThingID
GROUP BY t.name

One of the options in the survey is to select "No Color", which is saved in the database as null values.  These are converted to 255 so that it is the same as if they selected white.

That was pretty easy, but I also collect some basic, anonymous demographic information to enable breaking down the results a bit.  I ask for age, for instance.  What I wanted to do was to break down the results by age group, i.e. 20-29, 30-39, etc.  This is more complicated, of course, and I didn't want to get into doing a massive number of queries and loops, etc, to build up the results.  Fortunately I have recently gotten to know more about the SQL Server statement PIVOT.  PIVOT is made for creating just such reports as this.  This is somewhat more complicated than a simple PIVOT, because I don't want a column for every age, but I want to group the ages by decade.

I'll run through the ColdFusion/SQL code I used to make this work. 

First, I set a variable with an SQL expression to create the name of the age group, e.g. "40-49":

<cfset var AgeRange = "CONVERT(VARCHAR,Age/10*10) + '-' + CONVERT(VARCHAR,Age/10*10+9)">

I only want to include the age ranges for which I actually have data, so I do an initial query to get the age ranges in the data.  Note that the AgeRange is wrapped in brackets so that it can be used as the column name in the next query, e.g. "[40-49]".

<cfquery name="GetAges" datasource="#application.dsn#">
    SELECT  DISTINCT
            '[' + #preserveSingleQuotes(AgeRange)# + ']' AS AgeRange
    FROM    WCIPerson
    WHERE   Age IS NOT NULL
    ORDER BY AgeRange
</cfquery>

I can then build the PIVOT query including all of the age ranges.  I'm still not comfortable with the PIVOT syntax.  I've only used it a few times, and it's still hard for me to wrap my head around without looking at documentation and examples.  If you're new to PIVOT too, this site has a few good examples.  As you can see below, I reuse the AgeRange variable I set above twice.

<cfquery name="GetResults" datasource="#application.dsn#">
    SELECT  t2.name
            <cfloop query="GetAges">
                ,ISNULL(t2.#GetAges.AgeRange#,'0,255,255,255') AS #GetAges.AgeRange#
            </cfloop>
    FROM    ( SELECT    t.ThingID ,
                        t.name ,
                        #preserveSingleQuotes(AgeRange)# AS AgeRange,
                        CONVERT(VARCHAR, COUNT(*)) + ','
                        + CONVERT(VARCHAR, AVG(ISNULL(red, 255))) + ','
                        + CONVERT(VARCHAR, AVG(ISNULL(green, 255))) + ','
                        + CONVERT(VARCHAR, AVG(ISNULL(blue, 255))) AS Color
              FROM      WCIResponse r
                        INNER JOIN WCIThing t ON r.ThingID = t.ThingID
                        INNER JOIN WCIPerson p ON r.PersonID = p.PersonID
              WHERE     p.Age IS NOT NULL
              GROUP BY  t.ThingID ,
                        t.name ,
                        #preserveSingleQuotes(AgeRange)#
            ) AS t PIVOT ( MAX(color) FOR AgeRange IN ( #ValueList(GetAges.AgeRange)# ) ) AS t2
</cfquery>

The value returned here for each age range is actually a comma-delimited list of values, where the first value is the number of responses, and the next three are the RGB values.  When the data is pulled into the application (as JSON), I am able to parse these values and fill a table to colored blocks to show the selected colors for each age range.

Although the What Color Is...? site doesn't have a lot of data at this point, I believe this code should scale pretty well.  Of course in the unlikely event that the site becomes an internet phenomenon and gets millions of responses I may have to revisit this code.