Click here to Skip to main content
13,704,528 members
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

4.3K views
4 bookmarked
Posted 30 Dec 2017
Licenced MIT

What is a Dynamic Pivot Table?

, 30 Dec 2017
Rate this:
Please Sign up or sign in to vote.
In this puzzle, we’re going to learn how to create a dynamic pivot table using SQL Server.

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.

Dynamic Pivot Table Columns

The steps we’ll take to generate the dynamic pivot table are:

  1. Get a list of unique product lines
  2. Create a column list
  3. Construct a Pivot Table as SQL Statement
  4. 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.

Dynamic Pivot Table Column 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:

Dynamic Pivot Table Final Solution

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)

License

This article, along with any associated source code and files, is licensed under The MIT License

Share

About the Author

essentialSQL
Easy Computer Academy, LLC
United States United States
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.

I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.

It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.

I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.

Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.

It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.

Having video, pictures, and text really help to reinforce the point and enable learning.

And now I want to help you get the same results.

The first step is simple, click here ==> http://www.essentialsql.com/get-started-with-sql-server/

You may also be interested in...

Comments and Discussions

 
QuestionMessage Closed Pin
12-Jan-18 17:12
member12-Jan-18 17:12 
GeneralMy vote of 3 Pin
Jim_Snyder2-Jan-18 5:16
professionalJim_Snyder2-Jan-18 5:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web01-2016 | 2.8.180920.1 | Last Updated 30 Dec 2017
Article Copyright 2017 by essentialSQL
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid