I have already posted the article previously with my PIVOT. As PIVOT same, but
working reversely and it is UNPIVOT. I do not want to miss the post
using UNPIVOT. With PIVOT we can get aggregate
data with Rows as Columns. With UNPIVOT operation, we can change Columns to Rows without aggregation.
Let me prepare small example and explain as how how can we use UNPIVOT and changing column as Rows. Before go ahead to run the script of UnPivot, we will create a database and table objects.
Let me prepare small example and explain as how how can we use UNPIVOT and changing column as Rows. Before go ahead to run the script of UnPivot, we will create a database and table objects.
USE DEMO GO -- Creating table for demo IF (object_id('TblUnPivot','U') > 0) DROP TABLE TblUnPivot CREATE TABLE TblUnPivot ( ItemCode int, ItemName varchar(100), Red int, Green int, Blue int ) GO -- Inserting some sample records INSERT INTO TblUnPivot SELECT 1,'Samsung Mobile', 1, 1, 1 UNION ALL SELECT 2,'Nokia Mobile', 1, 2, 3 UNION ALL SELECT 3,'Motorola Mobile', 2, 3, 2 GONow we will check the original table data and also using UnPivot as well. So we will run both scripts for the same.
-- Getting table data select ItemCode, ItemName, Red, Green, Blue from TblUnPivot GO -- Getting Pivot and changing columns to rows SELECT ItemCode, ItemName, ItemColour, ItemCount FROM ( SELECT ItemCode, ItemName, Red, Green, Blue FROM TblUnPivot ) AS UP UNPIVOT ( ItemCount FOR ItemColour IN (Red, Green, Blue) ) AS upv GO
You can review here and see how the UNPIVOT is working. Let me share your experience with UNPIVOT.
Comments
Post a Comment