You are here:Home » tsql » Changing Rows to Columns Using PIVOT - SQL Server

Changing Rows to Columns Using PIVOT - SQL Server

During working with one logic, I got a chance to work with 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.
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.
-- Getting table data
SELECT 
ItemCode, 
ItemName, 
ItemColour
from TblPivot
GO

-- Getting agreegated 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.

4 comments:

  1. This works really well on known list of keys in the columns used to pivot. We used dynamic SQL to overcome this problem, it becomes less readable however.
    Mainly, do not let your programmers use the pivoted columns in WHERE clauses, performance drops dramatically.

    ReplyDelete
  2. Hi you are doing a great job. i was looking for this information. i found it on your page its really amazing. I like that information. I am sure that these are your own views. They are really awesome. You are really fantastic. Thanks Why didn’t I think about this? I hear exactly what you’re saying and I’m so happy that I came across your blog. You really know what you’re talking about, and you made me feel like I should learn more about this. Thanks for this; I’m officially a huge fan of your blog.

    car transportation service

    ReplyDelete
  3. Hi, This is great information. Im a beginner and really need some help. I am trying to produce a view from a table which has a customer, date and balance field. I am trying to extract the month from the date field and use this as a column alias displaying the balance in the relevant month column along with the customer field. I have tried select case statements however these give all months of the year when I am trying to narrow it down to the past 3 months. Using a WHERE clause after the case statements is producing 2 tables of results. Any help would be much appreciated.
    Dionne

    ReplyDelete