In this puzzle, we’re going to learn how to create a dynamic pivot table using SQL Server. A dynamic pivot table is a great way to summarize data. And given that Business Intelligence is a hot topic, knowing how to create one is key.
By reading this article, you’ll learn to create a dynamic pivot table for yourself, but before you read the entire article, try the puzzle. Any work you do, if you just get part of the answer, it helps to reinforce the concepts you’ll learn.
Solving puzzles is a great way to learn SQL. Nothing beats practicing what you’ve learned. Once you have figured out the puzzle, post your answer in the comments so we can all learn from one another.
SQL Puzzle Question
The Product Manager would like to create a pivot table showing by production line and location, the number of product parts in inventory.
The IOE summer intern has already written the query, and it works, but the pivot table is static, and it won’t include any newly added locations within the factory.
Can you take the intern’s query and turn it into a dynamic query?
Here is the query the intern wrote:
SELECT ProductLine,
[Debur and Polish],
[Final Assembly],
[Finished Goods Storage],
[Frame Forming],
[Frame Welding],
[Metal Storage],
[Miscellaneous Storage],
[Paint],
[Paint Shop],
[Paint Storage],
[Sheet Metal Racks],
[Specialized Paint],
[Subassembly],
[Tool Crib]
FROM
(
SELECT I.ProductID,
P.ProductLine as ProductLine,
L.Name as LocationNAme
FROM Production.ProductInventory I
INNER JOIN Production.Location L
ON L.LocationID = I.LocationID
INNER JOIN Production.Product P
ON I.ProductID = P.ProductID
) as PivotData
PIVOT
(
COUNT(ProductID)
FOR LocationName
IN (
[Debur and Polish],
[Final Assembly],
[Finished Goods Storage],
[Frame Forming],
[Frame Welding],
[Metal Storage],
[Miscellaneous Storage],
[Paint],
[Paint Shop],
[Paint Storage],
[Sheet Metal Racks],
[Specialized Paint],
[Subassembly],
[Tool Crib]
) ) AS PivotResult ORDER BY ProductLine
Good luck!
Note: Here is a great resource if you need help getting started with SQL Server and the Adventure Works database.
Creating a Dynamic Pivot Table – Step-By-Step
One look at the intern’s query (see above) and you’ll notice there is a long list of columns which are repeated in two locations within the query. These column names are unique location values, and due to the nature of how a pivot table works, need to be included in the query, for that location’s values to be displayed in the results.
To make the query dynamic, our task is to automatically generate the column name list based off the data. In other words, we want to replace the yellow highlighted portion with code to automatically generate the column list.
The steps we’ll take to generate the dynamic pivot table are:
- Get a list of unique product lines
- Create a column list
- Construct a Pivot Table as SQL Statement
- Execute the statement
Step 1. Get a List of Unique Locations
The first step is to construct a query to get a unique list of locations. The locations will be used as column names in our pivot table.
The query I used is:
SELECT DISTINCT L.Name
FROM Production.ProductInventory P
INNER JOIN Production.Location L
ON L.LocationID = P.LocationID
Which produces the following list.
Obtaining this list is just the first step. Now we need to take the location values and transform them into a string having a form similar to [location name 1], [location name 2], etc.
Step 2. Create a Column List
The plan is to create the column list and store it in a variable. This variable is then used to construct the pivot statement.
We’ll declare the @Columns variable as VARCHAR, and then using the query from above and input, build the column list.
DECLARE @Columns as VARCHAR(MAX)
SELECT @Columns =
COALESCE(@Columns + ', ','') + QUOTENAME(Name)
FROM
(SELECT DISTINCT L.Name
FROM Production.ProductInventory P
INNER JOIN Production.Location L
ON L.LocationID = P.LocationID
) AS B
ORDER BY B.Name
There are a couple of items to note. First, we use the built-in function QUOTENAME to properly format the column name. This ensure brackets [] are placed around the column names.
The statement SELECT @Columns = @Columns… iterates through the result and repeatedly concatenates the next location name to @Columns. This trick allows us to build a single value from many rows.
The COALESCE statement is used for the first assignment when @Columns is NULL. If it wasn’t used, then @Columns would always return NULL as concatenating any value to NULL results in NULL.
Here is the result of running this portion of the query:
[Debur and Polish], [Final Assembly], [Finished Goods Storage], [Frame Forming], [Frame Welding],
[Metal Storage], [Miscellaneous Storage], [Paint], [Paint Shop], [Paint Storage],
[Sheet Metal Racks], [Specialized Paint],
[Subassembly], [Tool Crib]
Notice it is exactly like the column list shown in the intern’s original query!
Step 3. Construct a Pivot Table as SQL Statement
Compare the following to the intern’s statement. There are a couple of key differences:
DECLARE @SQL as VARCHAR(MAX)
SET @SQL = 'SELECT ProductLine, ' + @Columns + '
FROM
(
SELECT I.ProductID,
P.ProductLine as ProductLine,
L.Name as LocationNAme
FROM Production.ProductInventory I
INNER JOIN Production.Location L
ON L.LocationID = I.LocationID
INNER JOIN Production.Product P
ON I.ProductID = P.ProductID
) as PivotData
PIVOT
(
COUNT(ProductID)
FOR LocationName IN (' + @Columns + ')
) AS PivotResult
ORDER BY ProductLine'
First, you see that we’re constructing a SQL statement in a VARCHAR. Also, notice that the column names, calculated in step two and housed in @Columns, are placed in the statement as well.
When complete, the variable @SQL will contain the complete pivot statement. It is the static pivot statement intern wrote.
Now all that remains is to execute the statement.
Step 4. Execute the Statement
The EXEC statement accepts a variable and executes the evaluated result as an SQL statement.
For instance, to run the statement ‘SELECT FirstName FROM Person.Person’, we could write
EXEC('SELECT FirstName FROM Person.Person')
However, what makes EXEC really useful, is the ability to pass it a variable whose contents contain a SQL statement. Consider the following:
DECLARE @SQL VARCHAR(MAX);
SET @SQL = 'SELECT FirstName FROM Person.Person';
EXEC(@SQL);
This produces the same result as the first example however, now the SQL is housed in a variable.
This is what allows us to write dynamic statements.
As you may have guessed, now that we’re through step three, we have a complete pivot statement in the variable @SQL. All we need to do now is execute it.
EXEC(@SQL);
We’ve now seen all the steps. Let’s see what the final solution looks like.
Dynamic Pivot Table Solution
To help put this in perspective, here is the final solution. Each color coded section corresponds to one of the four steps detailed above:
In case you want to try running this code on AdventureWorks, I’ve included a text version below:
DECLARE @Columns as VARCHAR(MAX)
SELECT @Columns =
COALESCE(@Columns + ', ','') + QUOTENAME(Name)
FROM
(SELECT DISTINCT L.Name
FROM Production.ProductInventory P
INNER JOIN Production.Location L
ON L.LocationID = P.LocationID
) AS B
ORDER BY B.Name
DECLARE @SQL as VARCHAR(MAX)
SET @SQL = 'SELECT ProductLine, ' + @Columns + '
FROM
(
SELECT I.ProductID,
P.ProductLine as ProductLine,
L.Name as LocationNAme
FROM Production.ProductInventory I
INNER JOIN Production.Location L
ON L.LocationID = I.LocationID
INNER JOIN Production.Product P
ON I.ProductID = P.ProductID
) as PivotData
PIVOT
(
COUNT(ProductID)
FOR LocationName IN (' + @Columns + ')
) AS PivotResult
ORDER BY ProductLine'
EXEC(@SQL)
