Using Pivot and UnPivot in Transact-SQL (Part-1):

Background:

One of the common scenarios we need to handle in the Data Integration process is to Pivot or UnPivot the input data received from external systems such as ERP or Finance systems.

Microsoft SQL-Server provides two very good Transact-SQL commands Pivot and UnPivot to handle such transformations.

In this part-1 series of this article I will cover how to use the Pivot command using T-SQL. In the part-2 series I will explain how to use the Un-Pivot command.

Scenario:

To demonstrate the use of the Pivot command, I am using a practical business scenario. In this example I am taking a fictitious fashion retailer ABC Fashions private Limited. This is a London based fashion retailer having branches across multiple locations in UK, Wales and Scotland. 

They get their actuals from their ERP systems every month by each of their locations. The extract format is following

• Location
• Product Name
• Account Name
• Version
• Month
• Year
• Value

For this example, I am just considering a sample set of data, which comprises a single product, single location and gross sales for each month in the year 2021.

Sample rows from this data are shown in the below table.

Fig  1: Sample Data

Now we want to pivot the months horizontally so that we can arrange the months across. 

Using Pivot Command:

Assume that we have the above data in a staging database in a table ERP_Staging. This table has the following columns and data types as shown in Fig 2 below.

   Fig 2 : Table Structure

The syntax for this command is given below

Fig 3 : Syntax for Pivot command

If we closely look at the syntax of the PIVOT command we can see that the SQL query command is broken in three sections.

1. Select section: 
2. From part
3. Pivot section.

In the select section we first specify a list of columns which are non-pivoted columns and then we specify all possible values of the column to be pivoted. 

So our select command will be as follow

Select Location, Product, Account, Year, Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec So I have highlighted the pivoted columns in bold. In our case as the column we want to pivot is the <Month> column, the rest of our columns before month are our unpivoted columns. Value column is part of aggregation which we want to show has value for each month. 

In the <From> section we use the query that will produce data.

In our case it will be

FROM (

Select Location, Product, Account, Year, Month, Value 

  From ERP_Staging

) as SourceTable

And finally a PIVOT section will be 

PIVOT(

Sum(Value) For Period In ( [Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]

)as pvt;

So the overall PIVOT command will be as shown below.

Select Location, Product, Account, [Year], Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec

FROM

(

Select Location, Product, Account, [Year], [Month], Value 

  From ERP_Staging

) as SourceTable

PIVOT(

Sum(Value) For [Month] In ( [Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])

)as pvt;

That’s it. We can see the result as shown below. 

Fig 4: The Pivot command  full query in SSMS

The  result of query execution is shown below.

Fig 5:  Results of query execution