Wednesday 3 June 2020

Unpivot in sql server


UNPIVOT relational operator is the reverse process of PIVOT relational operator. UNPIVOT relational operator converts data from the column level to the row level. UNPIVOT carries out almost the reverse operation of PIVOT, by rotating columns into rows. 
       

Let’s see the previous example


Pivot we will get the below result set.
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]))
as PVT
Result
   

In Unpivot we will transpose the column value into the rows
select * from (
 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]))
as PVT
)T UNPIVOT(Total_Sales FOR Sales_Year IN([2011],
                                        [2012],
                                        [2013],
                                        [2014])) AS upvt;

 


No comments:

Post a Comment

If you have any doubt, please let me know.

Popular Posts