PIVOT & UNPIVOT Operators in SQL Server 2008

April 19th, 2010 | Category: SQL server | Posted By: Kalyan Bandarupalli

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

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

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

kalyan Recommends

No comments

TechBubbles