This page has 0 threads | Add post

Dynamic Pivot Tables

Written by Andrew Gould

In this tutorial

Creating a List of Pivot Column Names
Creating a Comma-Separated String
Enclosing Column Names in Square Brackets
Removing the Trailing Comma
Concatenating the SQL Statement
Executing the SQL Statement

Perhaps the biggest limitation of the PIVOT operator is that you must explicitly list each column name that you want to create. Not only is this is immensely tedious when you have a large number of columns, you may not actually know what the column names will be. The solution is to create a dynamic pivot table using dynamic SQL.

Creating a List of Pivot Column Names

Start by creating a simple query to display all of the values that you want to become column headings in the final pivot table. In our example we'll select all of the country names using this statement:

SELECT CountryName

FROM tblCountry

The result of the query is shown below:

Your query may be more complex than the one shown above: the aim is to create a list of all column names for the final pivot table.

Creating a Comma-Separated String

The next step is to create a comma-separated string of the values from the query results. The code below shows how to do this:

--A variable to hold the complete string

DECLARE @Columns VARCHAR(MAX) = ''

--Concatenate each country with a comma

SELECT @Columns += (CountryName + ',')

FROM tblCountry

--Check the result

PRINT @Columns

The result of executing this code is shown below:

The result is a comma-separated list of country names.

Enclosing Column Names in Square Brackets

Each column name needs to be enclosed in a set of square brackets. We can achieve this by using the QUOTENAME function:

--A variable to hold the complete string

DECLARE @Columns VARCHAR(MAX) = ''

--Concatenate each country with a comma

--The QUOTENAME function adds square brackets around each value

SELECT @Columns += (QUOTENAME(CountryName) + ',')

FROM tblCountry

--Check the result

PRINT @Columns

The new result of executing the code looks like this:

Each country name is enclosed in square brackets.

Removing the Trailing Comma

At this point our list of column names contains an extra comma at the end of the string. One way to remove this is shown below:

--A variable to hold the complete string

DECLARE @Columns VARCHAR(MAX) = ''

--Concatenate each country with a comma

--The QUOTENAME function adds square brackets around each value

SELECT @Columns += (QUOTENAME(CountryName) + ',')

FROM tblCountry

--Remove the trailing comma

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

--Check the result

PRINT @Columns

The LEN function calculates the number of characters in the string. We subtract 1 from the result and use the LEFT function to return that number of characters from the left of the string.

Concatenating the SQL Statement

The next step is to generate the complete SQL statement that will create the pivot table and store the result in a string variable.

--A variable to hold the SQL string

DECLARE @SQL NVARCHAR(MAX) = ''

--Store the complete string in the variable

--Concatenate the list of column names

SET @SQL =

'WITH BaseData AS

(

SELECT

CountryName

,FilmID

FROM

tblFilm AS f INNER JOIN

tblCountry AS c

ON c.CountryID=f.FilmCountryID

)

SELECT * FROM BaseData

PIVOT

(

COUNT(FilmID)

FOR CountryName IN (' + @Columns + ')

) AS PivotTable'

--Check the result

PRINT @SQL

Printing the result is a good way to check that you've generated a valid SQL statement:

The complete SQL statement.

Executing the SQL Statement

The final step is to execute the dynamic SQL statement using either of the two techniques shown below:

--Execute the statement directly

EXECUTE (@SQL)

--Use a system stored procedure

EXECUTE sp_executesql @SQL

The final result should look like this:

A simple, dynamic pivot table.

From this point you can easily create more complex pivot tables by adding code to the dynamic SQL statement.

If you've enjoyed reading this blog, have a look at our SQL training page for much more information on how you can learn to write better SQL.

Previous
tutorial

Table-valued functions

Previous
part

Using the Pivot Operator in SQL Server

Top of page

Next
part

Next
tutorial

Triggers

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

Pivoting data

Dynamic Pivot Tables

Creating a List of Pivot Column Names

Creating a Comma-Separated String

Enclosing Column Names in Square Brackets

Removing the Trailing Comma

Concatenating the SQL Statement

Executing the SQL Statement

Head office

London

Manchester