SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Vista, etc.




download SQL Server 2014


T-SQL Pivot Tables in SQL Server - Pivot Table Tutorial with Examples



What is a 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

sample t-sql pivot table

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

pivot tables in sql



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.

pivot tables in sql

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 9 THEN 'September'
      WHEN 10 THEN 'October'
      WHEN 11 THEN 'November'
      WHEN 12 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 :

sql pivot tables with month names



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.

t-sql pivot table sample

Of course it is not best practise to write month names in Transact-SQL statements like in above examples. Instead of writing month name list manually, developers can use the SQL function ListMonthNames() that will return a list of month names along with month numbers. Then the sql script can be build by using a dynamic pivot table query like shown in following section.



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.

dynamic pivot tables in sql using t-sql pivot command

And other dynamic pivot table sample in SQL Server is the following sql pivot query. The output will list month names of a year as the columns of the output set and show total amounts of orders for that month period in the related cell. Please note, for the SQL function dbo.ListMonthNames() developers can refer to T-SQL tutorial List Month Names using SQL Functions

DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders =
 COALESCE(
  @PivotColumnHeaders + ',[' + [MonthName] + ']',
  '[' + [MonthName] + ']'
 )
FROM dummy.dbo.ListMonthNames()
ORDER BY monthid

DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
 SELECT *
 FROM (
  SELECT
   YEAR(OrderDate) [Year],
   DATENAME(MONTH, OrderDate) as [Month],
   SubTotal
  FROM Sales.SalesOrderHeader
 ) TableDate
 PIVOT (
  SUM(SubTotal)
  FOR [Month] IN (
   ' + @PivotColumnHeaders + '
  )
 ) PivotTable
'

EXECUTE(@PivotTableSQL)


T-SQL Pivot Table and Dynamic Pivot Table Queries in SQL Server

T-SQL Pivot Tables in SQL Server - Pivot Table Tutorial with Examples
T-SQL Dynamic Pivot Table Examples for SQL Server 2008
Create A Sample Bill of Material BOM SQL Query using T-SQL Pivot Command






Follow Kodyaz on Twitter

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums



Free Exam Vouchers









Copyright © 2004 - 2014 Eralper Yilmaz. All rights reserved.
Community Server by Telligent Systems