Pivot Magic in SQL

PIVOT is a relational operator used to provide a more readable or simpler view of a table’s data. This is achieved by rotating a rows data into columns using the query response to simply the query results.

UNPIVOT is the opposite. It rotates columns into rows.

PIVOTING can make the sales data below -













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 -