Prashanth Jayaram

Static and Dynamic SQL Pivot and Unpivot relational operator overview

October 12, 2018 by

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.

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.

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.

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.

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.

The following output is a monthly split of the sales data

Example 4: 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.


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…

See more

To boost SQL coding productivity, check out these free SQL tools for SSMS and Visual Studio including T-SQL formatting, refactoring, auto-complete, text and data search, snippets and auto-replacements, SQL code and object comparison, multi-db script comparison, object decryption and more.





Prashanth Jayaram

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

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
Prashanth Jayaram
T-SQL

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. 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

191 Views