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 insql server: https://bageshkumarbagi-msbi.blogspot.com/2020/06/pivot-in-sql-server.html

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;