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.