Creating Dynamic Pivot Table with QUOTENAME Function

In my previous article on the basic pivot operator, we saw how pivot operator could be used to convert rows to columns, resulting in pivot tables. We saw that there were three main steps to create a pivot table. The first step was selecting the base data. The second step was converting the base data to a table-valued expression, and the final step involved applying a pivot operator to the temporary data, which resulted in the pivot table.

Take a look at the example below.

USE schooldb

SELECT * FROM

(SELECT 
	city,
	total_score
FROM 
	student
)
AS StudentTable
PIVOT(
	AVG(total_score)
	FOR city IN ([London],[Liverpool],[Leeds],[Manchester])
) AS StudentPivotTable

Note: To create the dummy database and data, see the previous article on the Pivot Operator.

Limitations of Pivot Operator

However, there are certain limitations of pivot operator. Inside the pivot operator, we have to specify the aggregate field and columns that we want to pivot our data on. Finally, we also have to set the individual values for the column headings we want to create.

If we executed the script from the previous section, we would get the following result:

[table id=35 /]

The headings of the columns are the individual values inside the city column. We specified these values inside the pivot operator in our query.

The most tedious part of creating pivot tables is specifying the values for the column headings manually. This is the part that is prone to most errors, particularly if the data in your online data source changes. We can not be sure that the values we specified in the pivot operator will remain in the database until we create this pivot table next time.

For instance, in our script, we specified London, Liverpool, Leeds and Manchester as values for headings of our pivot table. These values existed in the Сity column of the student table. What if somehow one or more of these values are deleted or updated? In such cases, null will be returned.

A better approach would be to create a dynamic query that will return a full set of values from the column from which you are trying to generate your pivot table.

Creating a Dynamic Pivot Table

In this section, we will see how to create a dynamic pivot table.

This means that we will not need to manually specify the values for the column from which we are trying to generate our pivot table. Instead, we will set these values dynamically. For this purpose, we will use the “QUOTENAME” function.

As always, be sure you are well backed up before experimenting with a new code. See this article on backing up MS SQL databases if you’re not sure.

QUOTENAME Function

The “QUOTENAME” function formats selected results. Before explaining dynamic pivot it is worth looking at a quick working example of “QUOTENAME” function.

Take a look at the following query.

USE schooldb

SELECT QUOTENAME(city)+ ','
FROM student

By default, the “QUOTENAME” function wraps the selected items with square brackets. The output of the above query looks like this:

[table id=36 /]

Storing Column Names in a Variable

Though we have wrapped the column values with square brackets, we need to specify the values in the pivot operator in this format:

“[Leeds],[Liverpool],[London],[Manchester]”

To do this, we will need a variable.

USE schooldb

DECLARE @CityNames NVARCHAR(MAX) = ''

SELECT  @CityNames +=   QUOTENAME(city)+ ','
FROM 
(
	SELECT DISTINCT city
	FROM student
) AS CITIES

PRINT @CityNames

In the above query, we declared a variable “@CityNames” and initialized it with an empty string. Then, we used a  SELECT statement to select distinct city names from the city column and store them iteratively in the “@CityNames” variable. In each iteration, a distinct value in the city column along with a comma will be added to the “@CityNames” variable.

Then, we printed the value stored in this variable. The result of the above query will look like this:

“[Leeds],[Liverpool],[London],[Manchester],”

If you look at the output, there is a comma after the last value. We do not need that.

Removing a Trailing Comma

To remove a trailing comma, we will use a LEFT function that takes a string as its first argument. The second argument is the number of characters to be returned from that string starting from the first character. Take a look at the following query:

USE schooldb

DECLARE @CityNames NVARCHAR(MAX) = ''

SELECT  @CityNames +=   QUOTENAME(city)+ ','
FROM 
(
	SELECT DISTINCT city
	FROM student
) AS CITIES

SET @CityNames = LEFT(@CityNames, LEN(@CityNames)-1)

PRINT @CityNames

Here pay attention to this line of the script:

SET @CityNames = LEFT(@CityNames, LEN(@CityNames)-1)

In this line of the script, we used the LEFT function to get all the characters on the left side of the value stored in the “@CityNames” variable, starting from the first element. In the second argument, we used the LEN function to calculate the number of value elements stored in the “@CityNames” function and finally, we subtracted 1 from it. This removes the trailing comma from the string. The output will look like this:

[Leeds],[Liverpool],[London],[Manchester]

Converting SQL Query to String

Now, hopefully, we can use the “@CityNames” variable inside our pivot operator like this:

PIVOT(
	AVG(total_score)
	FOR city IN ( @CityNames )

However, we cannot use a variable inside our pivot operator. The alternative approach is to convert our complete SQL query to a string. Inside this string, we will hook our “@CityNames” variable.

USE schooldb

DECLARE @CityNames NVARCHAR(MAX) = ''
DECLARE @Query NVARCHAR(MAX) = '' 

SELECT  @CityNames +=   QUOTENAME(city)+ ','
FROM 
(
	SELECT DISTINCT city
	FROM student
) AS CITIES

SET @CityNames = LEFT(@CityNames, LEN(@CityNames)-1)

SET @Query =
'SELECT * FROM

(SELECT 
	city,
	total_score
FROM 
	student
)
AS StudentTable
PIVOT(
	AVG(total_score)
	FOR city IN (' + @CityNames +')
) AS StudentPivotTable'

PRINT @Query

Here we declared a variable “@Query” and stored our SQL query in this variable. Inside the pivot operator, we concatenated the value stored inside the “@CityNames” variable. To see how the executed query looks, we have printed the value of the “@Query” variable. The resulting query will look like this in the output:

SELECT * FROM

(SELECT 
	city,
	total_score
FROM 
	student
)
AS StudentTable
PIVOT(
	AVG(total_score)
	FOR city IN ([Leeds],[Liverpool],[London],[Manchester])
) AS StudentPivotTable

This is exactly the type of the query we want to execute. However, this is in the String format. The final step is to execute this SQL query stored as a text string. To do this, we will use Dynamic SQL.

Executing Dynamic SQL

We use the built-in procedure “sp_executesql” to execute dynamic SQL. We will use this stored procedure to execute the query stored in the @Query variable. Our final query that creates a dynamic pivot table looks like this:

USE schooldb

DECLARE @CityNames NVARCHAR(MAX) = ''
DECLARE @Query NVARCHAR(MAX) = '' 

SELECT  @CityNames +=   QUOTENAME(city)+ ','
FROM 
(
	SELECT DISTINCT city
	FROM student
) AS CITIES

SET @CityNames = LEFT(@CityNames, LEN(@CityNames)-1)

SET @Query =
'SELECT * FROM

(SELECT 
	city,
	total_score
FROM 
	student
)
AS StudentTable
PIVOT(
	AVG(total_score)
	FOR city IN (' + @CityNames +')
) AS StudentPivotTable'

EXECUTE sp_executesql @Query

When you execute the above query, you should see the following result:

[table id=37 /]

However, this time, we did not manually specify the values for the headings of the pivot table.  Instead, the headings have been calculated dynamically resulting in a dynamic pivot table.