In this article, we’ll walk-through the SQL Pivot and SQL Unpivot operators and how they can be useful to transpose SQL Server data. Also, we’ll discuss both static and dynamic ways to use PIVOT and UNPIVOT relational operators that can be used to transform aggregated distinct values as column(s) in the result-set by specifying all the column values in the PIVOT IN clause.
SQL Server pivot Introduction
There are several ways to transpose a dataset from rows to columns and columns to rows. SQL Pivot is one of the techniques that allows transposing of rows to columns and performs possible aggregations along the way. SQL PIVOT and SQL UNPIVOT relational operators transpose a table-valued two-dimensional data into another form of data. SQL PIVOT transposes a table-valued expression from a unique set of values from one column into multiple columns in the output and performs aggregations. SQL UNPIVOT performs the opposite operation of SQL PIVOT by transforming the columns of a table-valued expression into column values.
In most cases, the static pivoting technique suffices the business requirement. For example, the monthly sales forecast, yearly sales split, quarterly sales aggregation, etc, where the IN clause columns remain static. In some other instances, we need granular details and the table-value expression is more dynamic in nature and all the time new set of the expression are included in the table then dynamic PIVOT would be the best choice.
Note: In the above depiction, we can see that in the process of PIVOT, the column values are rotated from vertical to horizontal and UNPIVOTING is like rotating it from horizontal to vertical.
Syntax
SELECT <non-pivoted column>,
[pivot_column_1] AS <column alias>,
[pivot_column_2] AS <column alias>,
…
[pivot_column_n] AS <column alias>
FROM
(
<SELECT QUERY>)
AS <Alias for temporary data set>
PIVOT
(
<Aggregate function>( Aggregate column)
FOR
[<Pivot column will become column headers>]
IN ( [pivot_column_1], [pivot_column_2],
… [pivot_column_n] )
) AS <Pivot table alias>
< ORDER BY clause>;
Getting started with SQL Pivot
We’re going to start with pivoting and un-pivoting the data. By now you understand what PIVOT and UNPIVOT operators are in SQL Server. In simple terms, it is just another way to describe the process of turning distinct row values into their own columns is known as Pivoting. And the process of turning columns into rows is known as an un-pivoting.
Example 1: How to perform a SQL Pivot operation
Let’s picture the sample dataset derived from the AdventureWorks2014 database. In this case, you just got a simple dataset with SalesYear column along the one quantitative value TotalSales.
Now, the dataset is ready for pivoting. You’ve got SalesYear in the first column, TotalSales in the second column. If you asked to transpose the data, you have to take every distinct year in the column and these columns become the header of the pivot columns. So, 2011 becomes the first column, 2012 becomes its own column and so on. The actual values from the next column Totalsales, they stick with the pivot column headers. Now, the pivoting table would look like below.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT * FROM ( SELECT YEAR(SOH.OrderDate) as SalesYear, SOH.SubTotal as TotalSales FROM sales.SalesOrderHeader SOH JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId ) AS Sales PIVOT (SUM(TotalSales) FOR SalesYear IN ([2011],[2012],[2013],[2014])) as PVT |
The following image depicts the input data, Pivot syntax, SQL Pivot, and output results.
Example 2: How to perform a SQL Unpivot operation
Let’s now look in the other direction, un-pivoting, which is as simple as going from horizontal to vertical. In this case, you’d take all of those distinct columns that are selected, and it would turn those column headers into their own rows. Pivoting is like rotating from vertical to horizontal. And un-pivoting is like rotating from horizontal to vertical.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SELECT SalesYear, TotalSales FROM ( SELECT * FROM ( SELECT YEAR(SOH.OrderDate) AS SalesYear, SOH.SubTotal AS TotalSales FROM sales.SalesOrderHeader SOH JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId ) AS Sales PIVOT(SUM(TotalSales) FOR SalesYear IN([2011], [2012], [2013], [2014])) AS PVT ) T UNPIVOT(TotalSales FOR SalesYear IN([2011], [2012], [2013], [2014])) AS upvt; |
In the following example, the pivoted dataset is used to un-pivot the values. The key piece here is this was easy to do in getting transformed because we were able to create a pivot table as an intermediate step and then do the transformation using the unpivot operation.
Note: SQL pivot operation results in transposing the aggregated result into column but whereas SQL unpivot is not an exact reversal of transposing columns into the segregated values of rows. The unpivot operator will not split the aggregated results.
Example 3: How to get quarterly sales using SQL pivot operation
Let’s see a few more examples to understand the pivoting concepts better. In this case, we’ll see how to get quarterly sales data aggregated based on the quarterly sales.
Before we jump into the solution, it is always recommended to work on creating a dataset for the PIVOT operation.
The following example gives the aggregated monthly sales spread over the sales year.
|
1 2 3 4 5 6 7 8 9 |
SELECT YEAR(SOH.OrderDate) AS SalesYear, MONTH(SOH.OrderDate) AS SalesMonth, SUM(SOH.SubTotal) AS TotalSales FROM sales.SalesOrderHeader SOH JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId GROUP BY YEAR(SOH.OrderDate), MONTH(SOH.OrderDate) ORDER BY YEAR(SOH.OrderDate), MONTH(SOH.OrderDate); |
The following output shows the base data
Once you’re ready with the base data, you can apply the PIVOT operator. In the following example, the IN clause accepts a non-numerical character that got generated based on the DATEPART value. The DATEPART value is concatenated with ‘Q’ and then the values are fed to IN clause of PIVOT operator.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT SalesYear, ISNULL([Q1], 0) AS Q1, ISNULL([Q2], 0) AS Q2, ISNULL([Q3], 0) AS Q3, ISNULL([Q4], 0) AS Q4, (ISNULL([Q1], 0) + ISNULL([Q2], 0) + ISNULL([Q3], 0) + ISNULL([Q4], 0)) SalesYTD FROM ( SELECT YEAR(SOH.OrderDate) AS SalesYear, CAST('Q'+CAST(DATEPART(QUARTER, SOH.OrderDate) AS VARCHAR(1)) AS VARCHAR(2)) Quarters, SOH.SubTotal AS TotalSales FROM sales.SalesOrderHeader SOH JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId ) AS Data PIVOT(SUM(TotalSales) FOR Quarters IN([Q1], [Q2], [Q3], [Q4])) AS pvt ORDER BY SalesYear; |
The following output is a quarterly split of the sales data
Example 4: How to get monthly sales using a SQL pivot operation
Let us take a look into another example to see sales split data based on every month. In the following example, the IN clause values are generated as part of the DATEPART function. The DATEPART values are fed to IN clause of PIVOT operator.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
SELECT SalesYear, ISNULL([1], 0) AS Jan, ISNULL([2], 0) AS Feb, ISNULL([3], 0) AS Mar, ISNULL([4], 0) AS Apr, ISNULL([5], 0) AS May, ISNULL([6], 0) AS Jun, ISNULL([7], 0) AS Jul, ISNULL([8], 0) AS Aug, ISNULL([9], 0) AS Sep, ISNULL([10], 0) AS Oct, ISNULL([11], 0) AS Nov, ISNULL([12], 0) AS Dec, (ISNULL([1], 0) + ISNULL([2], 0) + ISNULL([3], 0) + ISNULL([4], 0) + ISNULL([4], 0) + ISNULL([5], 0) + ISNULL([6], 0) + ISNULL([7], 0) + ISNULL([8], 0) + ISNULL([9], 0) + ISNULL([10], 0) + ISNULL([11], 0) + ISNULL([12], 0)) SalesYTD FROM ( SELECT YEAR(SOH.OrderDate) AS SalesYear, DATEPART(MONTH, SOH.OrderDate) Months, SOH.SubTotal AS TotalSales FROM sales.SalesOrderHeader SOH JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId ) AS Data PIVOT(SUM(TotalSales) FOR Months IN([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) AS pvt; |
The following output is a monthly split of the sales data
Example 5: How to get monthly sales using dynamic SQL pivot operation
Let us address the monthly split of sales data using dynamic pivoting. So far, we discussed static pivot operations. In order to convert a static pivot operation to dynamic, we’ve to remove the hardcoded values from the IN clause. First, get the distinct expression from the dataset and then prepare a string by concatenating all the expressions. In the following example, the @columnname is used to concatenate all the expression. The concatenated expression is fed to pivot IN clause. The rest is a simple conversion of static SQL into a dynamic SQL and call @DML using sp_executesql stored procedure.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
DECLARE @dml AS NVARCHAR(MAX) DECLARE @ColumnName AS NVARCHAR(MAX) SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(Months) FROM (SELECT DISTINCT DATEPART(MONTH, SOH.OrderDate) Months FROM sales.SalesOrderHeader SOH JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId GROUP BY YEAR(SOH.OrderDate), DATEPART(MONTH, SOH.OrderDate)) AS Months --Prepare the PIVOT query using the dynamic SET @dml = N'SELECT SalesYear, ' +@ColumnName + ' FROM ( SELECT YEAR(SOH.OrderDate) AS SalesYear, DATEPART(MONTH, SOH.OrderDate) Months, SUM(SOH.SubTotal) AS TotalSales FROM sales.SalesOrderHeader SOH JOIN sales.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId GROUP BY YEAR(SOH.OrderDate), DATEPART(MONTH, SOH.OrderDate)) AS T PIVOT(SUM(TotalSales) FOR Months IN (' + @ColumnName + ')) AS PVTTable' --Print @DynamicPivotQuery --Execute the Dynamic Pivot Query EXEC sp_executesql @dml |
That’s all for now…
Wrapping Up
Thus far, we’ve demonstrated the basic concepts and several examples of SQL PIVOT and SQL UNPIVOT.
The sample is based on the SQL Server adventureworks2014 database. You can give a try and test all the samples yourself. If you’ve any question, please feel free to comment below…
My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.
View all posts by Prashanth Jayaram
- Stairway to SQL essentials - April 7, 2021
- A quick overview of database audit in SQL - January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021