Pivot Table

A pivot table is a frequently used method of summarizing and displaying especially report data by means of grouping and aggregating values.

Pivot tables are easily created by office users using Microsoft Excel or MS Access.
Since pivot table enables report builders and BI (Business Intelligence) specialists empower their presentation of reports and increase the visibility and unserstandability of mined data, pivot tables are common and preferred widely.
Pivot tables display data in tabular form. The pivot table formatting is not different than a tabular report formatting.
But the table columns are formed by the report data itself. I mean as a pivot table example, your report creator can build a report with years and months in the left side of the table, the main product lines are displayed as columns, and total sales of each product line in the related year and month is displayed in the cell content.
Actually you can easily answer what is pivot table question, if you have build OLAP reports or if you are familiar with OLAP reporting.
Pivot Table in sql grants the ability to display data in custom aggregations just like OLAP reports in SQL Server.
Simply pivot tables can be thought of transforming a table with its data into another table format. Just as building a sales report in months and product lines from Sales Orders table.

Microsoft SQL Server Pivot Table - How to Use Pivot Tables in SQL ?

Microsoft SQL Server has introduced the PIVOT and UNPIVOT commands as enhancements to t-sql with the release of MS SQL Server 2005.
In MS SQL Server 2008, we can still use the PIVOT command and UNPIVOT command to build and use pivot tables in sql.
T-SQL Pivot and Unpivot statements will transform and rotate a tabular data into an other table value data in sql .
Since Pivot / Unpivot are SQL2005 t-sql enhancements, databases which you want to execute pivot and unpivot commands should be at least at compatibility level 90 (SQL2005) or 100 (SQL2008).

T-SQL Pivot Syntax

T-SQL PIVOT syntax is not explicitly identified in the MSDN or on SQL Server BOL (BooksOnline) but general use of Pivot command can be summarized as follows :

SELECT
  [non-pivoted column], -- optional
  [additional non-pivoted columns], -- optional
  [first pivoted column],
  [additional pivoted columns]
FROM (
  SELECT query producing sql data for pivot
  -- select pivot columns as dimensions and
  -- value columns as measures from sql tables
) AS TableAlias
PIVOT
(
  <aggregation function>(column for aggregation or measure column) -- MIN,MAX,SUM,etc
  FOR [<column name containing values for pivot table columns>]
  IN (
    [first pivoted column], ..., [last pivoted column]
  )
) AS PivotTableAlias
ORDER BY clause -- optional

T-SQL Pivot Table Examples in AdventureWorks SQL Server sample database

Here is a sample pivot table example in sql for MS SQL Server AdventureWorks database.
The pivot example will rotate the colors as columns and display number of items in stock in a pivot table.

select
  PS.Name, P.Color, PIn.Quantity
from Production.Product P
inner join Production.ProductSubcategory PS
  on PS.ProductSubcategoryID = P.ProductSubcategoryID
left join Production.ProductInventory PIn
  on P.ProductID = PIn.ProductID

<!--[if !vml]--><!--[endif]-->
As you see by using the below sql pivot table query, we will code the select statement of the first pivot tables sample.
What is notable about the below pivot table query is that the pivot column headers are explicitly defined in the select script.
This means pivot table names should be identified by the sql developer or the administrator explicitly.
Otherwise, developers should code dynamic sql statements which will select pivot column names and build a resultant sql statement and execute the resultant pivot command using EXECUTE command.
We will see dynamic pivot tables in sql later with example codes.

select
  *
from
(
  select
    PS.Name, P.Color, PIn.Quantity
  from Production.Product P
  inner join Production.ProductSubcategory PS
    on PS.ProductSubcategoryID = P.ProductSubcategoryID
  left join Production.ProductInventory PIn
    on P.ProductID = PIn.ProductID
) DataTable
PIVOT
(
  SUM(Quantity)
  FOR Color
  IN (
    [Black],[Blue],[Grey],[Multi],[Red],
    [Silver],[Silver/Black],[White],[Yellow]
  )
) PivotTable

<!--[if !vml]--><!--[endif]-->

T-SQL Pivot Table Examples in AdventureWorks SQL Server sample database

Here is an other pivot table example t-sql code for AdventureWorks sample database.
This time we will list sales subtotal amounts in years due to months.
Below you can find the select query which is basic for pivot tables.

SELECT *
FROM (
  SELECT
    YEAR(OrderDate) [Year],
    MONTH(OrderDate) [Month],
    SubTotal
  FROM Sales.SalesOrderHeader
) TableDate
PIVOT (
  SUM(SubTotal)
  FOR [Month] IN (
    [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
  )
) PivotTable

And the output for this pivot table is as in the following grid format.
<!--[if !vml]--><!--[endif]-->
If you want to see the month names as pivot table headers, you can use the below pivot command.

SELECT *
FROM (
  SELECT
    YEAR(OrderDate) [Year],
    CASE MONTH(OrderDate)
      WHEN 1 THEN 'January'
      WHEN 2 THEN 'February'
      WHEN 3 THEN 'March'
      WHEN 4 THEN 'April'
      WHEN 5 THEN 'May'
      WHEN 6 THEN 'June'
      WHEN 7 THEN 'July'
      WHEN 8 THEN 'August'
      WHEN 8 THEN 'September'
      WHEN 8 THEN 'October'
      WHEN 8 THEN 'November'
      WHEN 8 THEN 'December'
    END as [Month],
    SubTotal
  FROM Sales.SalesOrderHeader
) TableDate
PIVOT (
  SUM(SubTotal)
  FOR [Month] IN (
    [January],[February],[March],[April],
    [May],[June],[July],[August],
    [September],[October],[November],[December]
  )
) PivotTable

And the output of this above t-sql pivot command is as follows :
<!--[if !vml]--><!--[endif]-->

SQL Pivot Tables with Two Column Examples

Here is an other example for pivot tables in sql again that can be run on SQL Server AdventureWorks sample database.
This Pivot table is formed of two columns on the left side of the pivot format and again as month values on the dimension.

SELECT *
FROM(
  SELECT
    YEAR(DueDate) [Year],
    CASE MONTH(DueDate)
      WHEN 1 THEN 'January'
      WHEN 2 THEN 'February'
      WHEN 3 THEN 'March'
      WHEN 4 THEN 'April'
      WHEN 5 THEN 'May'
      WHEN 6 THEN 'June'
      WHEN 7 THEN 'July'
      WHEN 8 THEN 'August'
      WHEN 9 THEN 'September'
      WHEN 10 THEN 'October'
      WHEN 11 THEN 'November'
      WHEN 12 THEN 'December'
    END as [Month],
    ProductID,
    OrderQty  FROM Production.WorkOrder
) WorkOrders
PIVOT
(
  SUM(OrderQty)
  FOR [Month] IN (
    [January],[February],[March],[April],
    [May],[June],[July],[August],
    [September],[October],[November],[December]
  )
) AS PivotTable
ORDER BY [Year], ProductID

Here is the pivot table view of the above t-sql select statement.
<!--[if !vml]--><!--[endif]-->

Dynamic Pivot Table Queries in SQL

Most of the case sql developers don't know what is the pivot table headers or they do not want to hard code it in their t-sql scripts.
In this case, t-sql developers or database administrators can build dynamic sql scripts for pivot tables and execute the resultant t-sql statement in order to have a dynamic pivot table structure.
Below is the sample sql codes for dynamic pivot tables for AdventureWorks sample database.

DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders =
  COALESCE(
    @PivotColumnHeaders + ',[' + cast(Name as varchar) + ']',
    '[' + cast(Name as varchar)+ ']'
  )
FROM Sales.SalesTerritory

DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
  SELECT *
  FROM (
    SELECT
      YEAR(H.OrderDate) [Year],
      T.Name,
      H.TotalDue
    FROM Sales.SalesOrderHeader H
    LEFT JOIN Sales.SalesTerritory T
      ON H.TerritoryID = T.TerritoryID
  ) AS PivotData
  PIVOT (
    SUM(TotalDue)
    FOR Name IN (
      ' + @PivotColumnHeaders + '
    )
  ) AS PivotTable
'

EXECUTE(@PivotTableSQL)

Output of the dynamic pivot table query is as seen in the below screenshot.
<!--[if !vml]--><!--[endif]-->