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
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
UNPIVOT SETS horizontal to vertical and pivot sets everything to horizontal from vertical