TechBubbles Microsoft Technology BLOG

PIVOT & UNPIVOT Operators in SQL Server 2008

PIVOT is a new T-SQL operator introduced in SQL Server 2008 which can be used to rotate rows into columns and can create a crosstab query. You can specify the PIVOT operator in FROM clause of your query.

Using PIVOT

Specify the desired values that you want to see in SELECT statement and FROM clause contains the PIVOT statement. Let us take an example

The following query uses ADVENTUREWORKS database which uses the order years as columns.

SELECT
  CustomerID,
  [2001] AS Y2001, [2002] AS Y2002, [2003] AS Y2003, [2004] AS Y2004
 FROM
 (
    SELECT CustomerID, DATEPART(yyyy, OrderDate) AS OrderYear, TotalDue
     FROM Sales.SalesOrderHeader
 ) AS piv
PIVOT
(
  SUM(TotalDue) FOR OrderYear IN([2001], [2002], [2003], [2004])
) AS child
ORDER BY CustomerID

The FROM clause create the values that you want to see in rows of the newly created columns. We used the FOR operator to list the values that we want to pivot in the OrderYear Column.

The Result for the above query will look like the following

image

USING UNPIVOT

UNPIVOT operator can be used to normalize the data that is already pivoted. Let us take the example. Create a Vendor Employee table and insert some values in it.

CREATE TABLE VEmployee
 (VendorID int,
  Emp1Orders int,
  Emp2Orders int,
  Emp3Orders int,
  Emp4Orders int,
  Emp5Orders int)
GO

INSERT INTO VEmployee VALUES(1, 4, 3, 5, 4, 4)

INSERT INTO VEmployee VALUES(2, 4, 1, 5, 5, 5)

INSERT INTO VEmployee VALUES(3, 4, 3, 5, 4, 4)

INSERT INTO VEmployee VALUES(4, 4, 2, 5, 4, 4)

INSERT INTO VEmployee VALUES(5, 5, 1, 5, 5, 5)

Select the values from above created table will look like this

image

SELECT VendorID, Employee, Orders AS NumberOfOrders
 FROM
  (SELECT VendorID, Emp1Orders, Emp2Orders, Emp3Orders, Emp4Orders, Emp5Orders
    FROM VEmployee
  ) AS p
UNPIVOT
(
  Orders FOR Employee IN
   (Emp1Orders, Emp2Orders, Emp3Orders, Emp4Orders, Emp5Orders)
) AS unpvt

                      

The Results are look like the following

image

About the author

Kalyan Bandarupalli

My name is kalyan, I am a software architect and builds the applications using Microsoft .NET technologies. Here I am trying to share what I feel and what I think with whoever comes along wandering to Internet home of mine.I hope that this page and its contents will speak for me and that is the reason I am not going to say anything specially about my self here.

2 Comments

  • Hi Kalyan,
    First of all ,pivot and unpivot are introduced in sql server 2005 not in sql 2008 . This is good article but i think u have to explain it more deeply. Because nobody understand from ur article that how pivot and unpivot works internally .

    With regrds,
    SR Dusad

TechBubbles Microsoft Technology BLOG

Follow me

Archives

Tag Cloud