PIVOT in SQL Server

Changing Rows to Columns Using PIVOT - SQL Server

While working with a logic, I got a chance to work on PIVOT operation. Sometime we need do require row data as a column in our custom logic, then we can use some temp table and then populate aggregate data in a temp table. But With PIVOT we can do it very easily. Let me prepare small example and explain as how how can we use PIVOT and get row data as a column.

Before going ahead to run the script of Pivot, we will create a database and table objects.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

CREATE DATABASE DEMO GO USE DEMO GO -- Creating table for demo IF (object_id('TblPivot','U') > 0) DROP TABLE TblPivot CREATE TABLE TblPivot ( ItemCode int, ItemName varchar(100), ItemColour varchar(50) ) GO -- Inerting some sample records INSERT INTO TblPivot SELECT 1,'Samsung Mobile','Red' UNION ALL SELECT 2,'Nokia Mobile','Blue' UNION ALL SELECT 3,'Nokia Mobile','Green' UNION ALL SELECT 4,'Motorola Mobile','Red' UNION ALL SELECT 5,'Samsung Mobile','Green' UNION ALL SELECT 2,'Nokia Mobile','Blue' UNION ALL SELECT 1,'Samsung Mobile','Red' UNION ALL SELECT 2,'Nokia Mobile','Blue' GO

Now we will check the original table data and aggregated data using Pivot. So we will run both scripts for the same.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

-- Getting table data SELECT ItemCode, ItemName, ItemColour from TblPivot GO -- Getting aggregated data using Pivot and converted rows to column SELECT *  FROM  (     SELECT  ItemCode,  ItemName,  ItemColour     FROM TblPivot  ) AS P PIVOT (   Count(ItemName) FOR ItemColour IN (Red, Blue, Green) ) AS pv GO

You can review here and see how The PIVOT is working. Let me share your experience with PIVOT operation.