Reverse PIVOT table in SQL Server

convert rows to columns sql pivot and unpivot table sql server pivot and unpivot examples in sql server undo pivot table sql server In this video we will discuss if it's always possible to reverse what PIVOT operator has done using UNPIVOT operator. Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help. https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1 Is it always possible to reverse what PIVOT operator has done using UNPIVOT operator. No, not always. If the PIVOT operator has not aggregated the data, you can get your original data back using the UNPIVOT operator but not if the data is aggregated. Let us understand this with an example. We will use the following table tblProductSales for the examples in this video. SQL Script to create tblProductSales table Create Table tblProductSales ( SalesAgent nvarchar(10), Country nvarchar(10), SalesAmount int ) Go Insert into tblProductSales values('David','India',960) Insert into tblProductSales values('David','US',520) Insert into tblProductSales values('John','India',970) Insert into tblProductSales values('John','US',540) Go Let's now use the PIVOT operator to turn ROWS into COLUMNS SELECT SalesAgent, India, US FROM tblProductSales PIVOT ( SUM(SalesAmount) FOR Country IN (India, US) ) AS PivotTable Now let's use the UNPIVOT operator to reverse what PIVOT operator has done. SELECT SalesAgent, Country, SalesAmount FROM (SELECT SalesAgent, India, US FROM tblProductSales PIVOT ( SUM(SalesAmount) FOR Country IN (India, US) ) AS PivotTable) P UNPIVOT ( SalesAmount FOR Country IN (India, US) ) AS UnpivotTable The above query reverses what PIVOT operator has done, and we get the original data back as shown below. We are able to get the original data back, because the SUM aggregate function that we used with the PIVOT operator did not perform any aggregation. Now execute the following INSERT statement to insert a new row into tblProductSales table. Insert into tblProductSales values('David','India',100) With this new row in the table, if you execute the following PIVOT query data will be aggregated SELECT SalesAgent, India, US FROM tblProductSales PIVOT ( SUM(SalesAmount) FOR Country IN (India, US) ) AS PivotTable Now if we use UNPIVOT opertaor with the above query, we wouldn't get our orginial data back as the PIVOT operator has already aggrgated the data, and there is no way for SQL Server to know how to undo the aggregations. SELECT SalesAgent, Country, SalesAmount FROM (SELECT SalesAgent, India, US FROM tblProductSales PIVOT ( SUM(SalesAmount) FOR Country IN (India, US) ) AS PivotTable) P UNPIVOT ( SalesAmount FOR Country IN (India, US) ) AS UnpivotTable Notice that for SalesAgent - David and Country - India we get only one row. In the original table we had 2 rows for the same combination. Link for all dot net and sql server video tutorial playlists https://www.youtube.com/user/kudvenkat/playlists?sort=dd&view=1 Link for slides, code samples and text version of the video http://csharp-video-tutorials.blogspot.com/2015/10/reverse-pivot-table-in-sql-server.html