This is quite a popular question and I have never written about this on my blog. A Pivot Table can automatically sort, count, and total the data stored in one table or spreadsheet and create a second table displaying the summarized data. The PIVOT operator turns the values of a specified column into column names, effectively rotating a table.
USE AdventureWorks GO SELECT [CA], [AZ], [TX] FROM ( SELECT sp.StateProvinceCode FROM Person.Address a INNER JOIN Person.StateProvince sp ON a.StateProvinceID = sp.StateProvinceID ) p PIVOT ( COUNT (StateProvinceCode) FOR StateProvinceCode IN ([CA], [AZ], [TX]) ) AS pvt;
“If we PIVOT any table and UNPIVOT that table do we get our original table?”
I really think this is a good question. Answers is Yes, you can but not always. When we pivot the table we use aggregated functions. If due to use of this function if data is aggregated, it will be not possible to get the original data back
Looking at the final PIVOT – UNPIVOT table is little different from the original table and it contains the sum of the two records which we have observed in the PIVOT table. You can see that result which are displayed in red fonts are summed.
Reference: Pinal Dave (http://blog.SQLAuthority.com)