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
