Executive Summary:
T-SQL's PIVOT statement, which was introduced in Microsoft SQL Server 2005, has one key limitation: You must know about and include all the PIVOT column names in the PIVOT query. Creating a script that will work regardless of any new columns might seem impossible, but it's not. You just need to follow a two-step technique to achieve dynamic pivoting. |
SQL Server 2005's introduction of the PIVOT statement is a welcomed addition for individuals who routinely create reports that work with aggregate data because it makes the pivoting process simple. However, the PIVOT statement has one key limitation: You must know about and include all the PIVOT column names when you're writing the code. In some cases, this isn't a major limitation. For example, if you're creating a report that contains monthly aggregations, the column names will always be the month names. However, there are many situations that require a more robust, dynamic solution. I'll describe a fairly common business scenario in which knowing all the column names isn't possible and show you how to create a dynamic PIVOT query so that isn't a problem.
The Scenario
Suppose you have a database in which instructors enter students' names, assignments, and grades—and you need to create a report that lists the grades students received on their assignments. The code in Listing 1 creates and loads a sample data set with students, assignments, and grades. (You can download this listing and the other listings presented here by clicking the 102722.zip hotlink at the top of the page.)
Listing 1: Code to Create the Data Set |
 |
For simplicity sake, this information is loaded into a non-normalized table. However, all the examples could easily be expanded to work with proper database structures.
If you execute the command
SELECT * FROM DemoTable
against the sample data set, you'll get the grade report shown in Figure 1.
Figure 1: The default report |
 |
As you can see, this report provides all the necessary information, but it's hard to determine how each student is doing. It would be much better to create a report that looks like the one in Figure 2. This pivoted grade report provides the same information but is much easier to work with from an end-user perspective.
Figure 2: The pivoted report (click to enlarge) |
 |