Pivot in sql server

Pivot in SQL server is used when we want to transform data from row-level to the column level.

Suppose we have data in the row and we want our final output in the column-wise in this case we are using the pivot.

Basically, we are using the pivot for the reporting.

Let’s see the example.

For the demo here we are using the “AdventureWorks2017” database.

We have a sales table “sales.SalesOrderHeader” which has information about sales order.

 

The business wants a report which shows the year wise total sales. From the above result set, it will be tides task to get the desired result to the business. If we use pivot we can transpose the value of the row to the column so business easily gets the desired result.

Like below.

 

Let’s see how to use the pivot in SQL server

Syntax

   

 For the better understanding here we have divided the syntax into 5 parts.

Part 1:

First of all, we need to get the raw data for which we want to pivot.

Select  year(so.OrderDate) as sales_Year ,st.Name ,st.CountryRegionCode ,st.[Group] ,so.SubTotal as Total_Sales  FROM sales.SalesOrderHeader so  join Sales.SalesTerritory st  on st.TerritoryID=so.TerritoryID

 

  

Part 2:

Here based on the requirement we need to take the aggregation function like sum, avg, Min, Max etc.

In this example, we are taking SUM().

Part 3:

In this example, we are creating a static pivot. We have the fixed column name so in this part, we will give the column name.

Part 4:

This is the final output, so based on our requirement we need to provide the column name and it includes non – pivot and pivot columns name.

select Name, CountryRegionCode, [Group], [2011],[2012],[2013],[2014] from ( Select  year(so.OrderDate) as sales_Year ,st.Name ,st.CountryRegionCode ,st.[Group] ,so.SubTotal as Total_Sales  FROM sales.SalesOrderHeader so  join Sales.SalesTerritory st  on st.TerritoryID=so.TerritoryID  )  as sales  pivot  (   SUM(Total_Sales)   FOR Sales_Year IN ([2011],[2012],[2013],[2014])  )pvt

See the result

 

Part 5

Order by clause is optional. Suppose we want to get the Name in accessing or descending order we need to specify.

select Name, CountryRegionCode, [Group], [2011],[2012],[2013],[2014] from ( Select  year(so.OrderDate) as sales_Year ,st.Name ,st.CountryRegionCode ,st.[Group] ,so.SubTotal as Total_Sales  FROM sales.SalesOrderHeader so  join Sales.SalesTerritory st  on st.TerritoryID=so.TerritoryID  )  as sales  pivot  (   SUM(Total_Sales)   FOR Sales_Year IN ([2011],[2012],[2013],[2014])  )pvt  order by name

   

 

Here we the sorted result set.

This the example of static pivot.

In the next post, we will share the how-to create a dynamic pivot.

I hope this will help to understand the concept of the pivot.

Keep reading J