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.