The PIVOT keyword in Microsoft SQL Server is a godsend when you need to manipulate row values into their own columns. Unfortunately, to use this PIVOT keyword, you need to supply all of the possible values that you would like to PIVOT into columns in the query. What if there you do not know the possible values until runtime? In a case like this, you’ll need to have another trick up your sleeve.
For situations like this, where you do not know all of the columns you need to PIVOT beforehand, a dynamic PIVOT will do the job. So, the next question I’m sure you have is how to use a dynamic PIVOT. The concept of this is rather simple.
As I mentioned above, in order to use the PIVOT keyword, you must supply a list of values that will be pivoted into columns. The basics of using a dynamic PIVOT is to first compile the list of values that you want to PIVOT. Then, using this list of values, we can create the dynamic PIVOT. The example below should help illustrate this concept better.
Let’s first assume that we have a table of sales by sales person names Sales. It contains 3 columns:
- SalesDate (datetime)
- SalesPerson (varchar(50))
- SalesAmount (money)
This table stores the amount of sales generated by a sales person for any specific date. A user of this system needs the data in this table manipulated to show total sales by sales person by month. The caveat is that they do not want this data on separate rows. They want each month on a separate row with each sales person appearing as a column. Something like the format below:
Month | Sales Person 1 | Sales Person 2 | Sales Person 3 |
January | 500 | 100 | 150 |
March | 100 | 780 | 50 |
How can we write a query to display our data in this way? Obviously a PIVOT is needed since we want to make columns out of the different values in the SalesPerson column. The tricky part is we don’t know how many or what values will live in the SalesPerson column. This is where a dynamic PIVOT comes in handy so we can pivot all of the distinct values we find in the SalesPerson column.
You first want to start by getting a distinct list of values from the SalesPerson column and formatting these into a comma-separated string. To do this, we can use the COALESCE keyword like so:
[code language=’sql’]
DECLARE @tempTable TABLE ([SalesPerson] varchar(50))
INSERT INTO @tempTable
SELECT SalesPerson
FROM Sales
GROUP BY SalesPerson
DECLARE @pivotCols varchar(MAX)
SELECT @pivotCols = COALESCE(@pivotCols + ‘, [‘ + SalesPerson + ‘]’, ‘[‘ + SalesPerson + ‘]’) FROM @tempTable
DECLARE @selectCols varchar(MAX)
SELECT @selectCols = COALESCE(@selectCols + ‘, ISNULL([‘ + SalesPerson + ‘], 0) AS [‘ + SalesPerson + ‘]’, ‘ISNULL([‘ + SalesPerson + ‘], 0) AS [‘ + SalesPerson + ‘]’) FROM @tempTable
[/code]
This code is pretty straightforward. We first pull the distinct list of values from the SalesPerson column (this is the column we are eventually going to PIVOT so we need the values in this column) and push these values into a temporary storage location. Next, we use the COALESCE keyword to build two separate comma-separated strings. One comma separated string (@pivotCols) is the list of values that we are pivoting into columns. The other comma-separated value (@selectCols) is used to SELECT the appropriate values after the data has been pivoted taking into account that some columns may be NULL after the PIVOT.
Once we have the comma-separated list of columns, we can begin constructing our PIVOT statement. The dynamic PIVOT statement is illustrated below (keep in mind that since we are building the PIVOT dynamically, we need to first construct the SQL query in a temporary variable, and then EXECUTE it with the sp_executesql stored procedure):
[code language=’sql’]
DECLARE @query nvarchar(MAX)
SET @query = ‘
SELECT Month, ‘ + @selectCols + ‘
FROM
(
SELECT MONTH(SalesDate) AS Month, SalesPerson, SUM(SalesAmount) AS SalesAmount
FROM Sales
GROUP BY MONTH(SalesDate), SalesPerson
) S
PIVOT
(
SUM(S.SalesAmount) FOR [SalesPerson] IN (‘ + @pivotCols + ‘)
) P’
EXECUTE sp_executesql @query
[/code]
As you can see, the actual PIVOT query is not long and should be easy to understand once I’ve explained what I’m doing. To start, we are actually selecting from another query which returns the data in a format where the SalesAmount is summed and grouped by SalesPerson and Month. We are then pivoting this result set based on all of the values in the SalesPerson column which we compiled into the @pivotCols variable earlier. Finally, we are pulling the Month and the columns which we pivoted, using the @selectCols variable. Note that it may help to see what the final query is doing by using the PRINT command to see what the query looks like prior to executing it.
And that’s it for creating a dynamic PIVOT query in Microsoft SQL Server. I’ve found the dynamic PIVOT to be extremely useful when generating various reports requested by users, especially for reports where I need to PIVOT the data based on a date. You can download SQL scripts to create the table schema, populate the data I used, and the actual PIVOT query here: https://www.michaelbowersox.com/wp-content/uploads/2013/03/Dynamic-Pivot.zip
Until next time (hopefully not another year), happy coding!