Changing Columns to Rows Using UNPIVOT - SQL Server by serverku

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.

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

GO

Now 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.