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.
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
- Product Name
- Account Name
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.
- Select section:
- From part
- 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
Select Location, Product, Account, Year, Month, Value
) as SourceTable
And finally a PIVOT section will be
Sum(Value) For Period In ( [Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]
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
Select Location, Product, Account, [Year], [Month], Value
) as SourceTable
Sum(Value) For [Month] In ( [Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])
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