How to use Pivot to transform Rows into Columns in SQL Server

Posted on Jan 13 2014 - 10:53am by admin

Sometimes while querying the data, we would like to see the data in the rows field as columns so that it provides more clarity to the end user regarding the data. We can achieve the following by using a PIVOT keyword in SQL Server.

To do this, let us consider some sample data as shown below.

CREATE TABLE Sales
(
SaleWeek VARCHAR(10),
SalesAmount INT
)
GO

INSERT INTO Sales(SaleWeek, SalesAmount) VALUES('Sunday', 10)
INSERT INTO Sales(SaleWeek, SalesAmount) VALUES('Monday', 20)
INSERT INTO Sales(SaleWeek, SalesAmount) VALUES('Tuesday', 30)
INSERT INTO Sales(SaleWeek, SalesAmount) VALUES('Wednesday', 40)
INSERT INTO Sales(SaleWeek, SalesAmount) VALUES('Thursday', 50)
INSERT INTO Sales(SaleWeek, SalesAmount) VALUES('Friday', 60)
INSERT INTO Sales(SaleWeek, SalesAmount) VALUES('Saturday', 70)

Once you run the following script you should see the data as below.
p1

Syntax of a PIVOT is as follows

SELECT , 
FROM (source-query-that-produces-the-data) AS alias
PIVOT
( 
FOR  IN () AS pvt_alias

Using the syntax of a PIVOT, let us write our own PIVOT statement to see the row level values as columns.

SELECT pvt.*
FROM (SELECT SaleWeek, SalesAmount FROM Sales) AS a
PIVOT 
(SUM(SalesAmount) FOR SaleWeek  ([Sunday], [Monday], [Tuesday], [Wednesday] , [Thursday], [Friday], [Saturday])) AS pvt

Execute the statement and you should be able to see the Pivoted data as below.
p2

*** Please Note that PIVOT works only with the database compatibility level of 90 meaning with SQL SERVER 2005 and higher. ***

*** Like Us on Facebook to get instant notifications about new posts ***
*** Please leave your feedback below in the comments section if you have found this post useful ***

2 Comments so far. Feel free to join this conversation.

  1. Anonymous April 22, 2014 at 7:10 am -

    i have a data set that i would like to pivot, it looks like this :
    Year Date Item Description Branches Today Order Tomorrow Order Day 3 Order
    2014 22/04/2014 12345 ballerina doll 1690 18 0 0
    2014 22/04/2014 12345 2576 0 0 0
    2014 22/04/2014 12345 2666 0 18 0
    2014 22/04/2014 12345 2500 0 0 0
    2014 22/04/2014 12345 4720 0 0 0
    2014 22/04/2014 12345 2590 0 0 0
    2014 22/04/2014 12345 2540 0 0 0

  2. admin April 22, 2014 at 9:08 am -

    How do you want to pivot it? It would have been better if you provided a sample output as well.. Please use the contact page to send the full scenario and I can reply you with the code. Thanks

Leave A Response