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.
Related: Pivoting Data
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.
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.
Now that you know a pivoted report is the best way to present the information, let's look at two distinct ways to create it: static pivoting and dynamic pivoting.
Static Pivoting
If you're familiar with using the PIVOT statement, you might be thinking that getting the results in Figure 2 is easy to accomplish. I agree that it is very easy for this specific subset of data. You just need to use a script like that in Listing 2.
|
Listing 2: Static PIVOT Script |
 |
This script first uses a common table expression (CTE) to obtain the data to be pivoted, then uses a PIVOT query to convert the data into the desired format. (If you're unfamiliar with how static pivoting works, see the article "Create Pivoted Tables in 3 Steps".)
As I mentioned previously, the problem with static pivoting is that you need to know about and include every column name when you write the code. If the database includes a column that you didn't know about, that column won't show up in the pivoted table. For example, suppose an instructor adds a column named Assignment4 to our sample data set, but doesn't let you know about the additional column. When you run the static PIVOT script in Listing 2, the information about Assignment4 wouldn't show up in the results.
Dynamic Pivoting
Creating a script that will work regardless of any new columns entered into the system might seem impossible, but it's not. You just need to use a two-step technique:
- Retrieve the column names and store them in a variable.
- Build a pivot query using the variable's values.
Using this technique, I created the dynamic PIVOT script in Listing 3. Let's take a detailed look at how this script works.
|
Listing 3: Dynamic PIVOT Script |
 |
Callout A in Listing 3 highlights the code that retrieves the column names. This code relies on the COALESCE statement and QUOTENAME function to work correctly.
After declaring the @Columns variable, the code uses the COALESCE statement to return the first non-null item and concatenate the column names as the SELECT statement builds the result set. Some people might dislike this approach because the @Columns variable is used on both sides of the assignment operator (=). However, I like using this approach because it's simple and it works.
When you perform these types of operations, security is extremely important. For that reason, the QUOTENAME function is used to get the column names. This function returns a safe column name that can be used to perform the pivot. For example, if someone created a column named myCourse \] for fun, a script error wouldn't be generated. Without the QUOTENAME function, the script would be susceptible to a second-order SQL injection attack, where persisted data could be used to create a SQL injection attack. Functionality and security are both key concerns in this example, which is why it differs from many other T-SQL examples that you might find on the Internet.
The code in callout A fills the @Columns variable with a list that looks like
\[Assignment1\], \[Assignment2\],\[Assignment3\], \[Assignment4\]
With this list, you can build the dynamic PIVOT query. As callout B in Listing 3 shows, the dynamic PIVOT query is a modified version of the static PIVOT query. Instead of hard-coding the column names in the SELECT statement, the dynamic PIVOT query uses the names in the @Columns variable. After the @Columns variable's values are concatenated in the appropriate spots in the PIVOT query, the EXECUTE statement runs the query. The results will look like those in Figure 2.
Note that this example was simplified to better demonstrate how to achieve dynamic pivoting. However, this technique will work for more complex scenarios as well. You just need to expand the dynamic PIVOT query.
A Valuable and Time-Saving Technique
The two-step dynamic pivoting process is a valuable and time-saving technique. Regardless of how many columns are added to a database, you'll always get accurate results—and those results will be easier for end users to work with. Plus, you'll save time because you can use this technique for almost any kind of situation.
