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
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
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 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 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.
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.
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