SQL Pivot Table in SQL Server 2008 - SQL Pivot Table Query Example
T-SQL developers who wonder the answer of question "What is a pivot table ?" can read the short description for pivot tables.
The definitions of a pivot table in SQL Server 2005 or a pivot table in SQL Server 2008 is not much different than the given description in that sql pivot table tutorial.
Although the definition is as simple as issuing the importance and use of grouping and aggregating values in sql pivot data, t-sql developers will definitely need sql pivot table examples to fully understand how to use pivot tables in SQL Server 2005 and in SQL Server 2008.
SQL developers can find sql pivot table examples as well as other SQL Server tutorials in this web site. I've added a short list of pivot table training links at the end of this tutorial.
If you want to run the sql pivot table query example on your installed SQL Server databases, I can advice you to work on MS SQL Server 2008 AdventureWorks sample database.
I'll be giving examples for sql pivot table using sample data in SQL Server 2008 AdventureWorks database.
In case that AdventureWorks sample database is not installed on your SQL Server 2005 or SQL Server 2008 instance, you can find from where and how to download AdventureWorks and install sample database for Microsoft SQL Server 2008 at this short article.
T-SQL is being improved rapidly with the new t-sql enhancements in SQL Server 2005 and in SQL Server 2008.
It is now easier for sql developers and sql administrators to build and work with an sql pivot table query.
In this pivot table SQL Server 2008 AdventureWorks database Product, ProductCategory and ProductSubcategory sql database tables will be used for creating pivot tables.
Using pivor table with the above 3 sample database tables, sql pivot data will display numbers of products in each product category where headers of the columns in sql pivot table query will be the product category description.
Sometimes sql programmers and sql database administrators require to create t-sql dynamic pivot tables since it is not possible to determine the headers of the pivot table field list.
When all pivot table field can not be defined prior running the pivot table query, an sql dynamic pivot table query becomes a must.
You can find samples at tsql tutorial T-SQL Dynamic Pivot Table Examples for SQL Server 2008 where rules of dynamic pivot tables can be implied on SQL Server 2005 databases.
Here below you can see the tables for t-sql pivot table example and their relations in a SQL Server database diagram screenshot :
Sql pivot tables are common methods of displaying summary data for reporting purposes by use of grouping and aggregating data.
If we want to display data in tabular form, we can use the below t-sql query instead of t-sql pivot table formatting.
WITH CTE AS
(
SELECT
PC.Name ProductCategory,
P.ProductID
FROM Production.Product P
INNER JOIN Production.ProductSubcategory SC
ON SC.ProductSubcategoryID = P.ProductSubcategoryID
INNER JOIN Production.ProductCategory PC
ON PC.ProductCategoryID = SC.ProductCategoryID
)
SELECT
ProductCategory,
COUNT(*) Quantity
FROM CTE
GROUP BY ProductCategory
As you can see from the output of the above T-SQL CTE query, the output data is not in desired sql pivot table format although it outputs meaningfull sql data.
Now it is time for formatting the above t-sql query into a t-sql pivot table query.
First of all, remember the t-sql pivot table syntax.
SQL developers can also refer to sql tutorial for T-SQL PIVOT syntax.
I will copy a simple version of the t-sql pivot table syntax here as you can see in the below sql code block.
SELECT
*
FROM (
SELECT
[pivot columns as dimensions],
[value columns as measures]
FROM [sql tables]
) AS Data
PIVOT
(
<aggregation function>(column for aggregation or measure column)
-- like COUNT,MIN,MAX,SUM,etc
FOR [(pivot column)]
IN (
[first pivoted value for pivot column header],
...,
[last pivoted value for pivot column header]
)
) AS PivotTable
Example of T-SQL Pivot Table in SQL Server 2008 AdventureWorks Sample Database
Now let's create pivot table query using the above t-sql syntax for pivot table.
Here is an example for the pivot table in SQL Server 2008.
The pivot example will move the product categories to the header columns and display number of products in each product categort as the measure in pivot table query.
SELECT
*
FROM (
SELECT
PC.Name ProductCategory,
P.ProductID
FROM Production.Product P
INNER JOIN Production.ProductSubcategory SC
ON SC.ProductSubcategoryID = P.ProductSubcategoryID
INNER JOIN Production.ProductCategory PC
ON PC.ProductCategoryID = SC.ProductCategoryID
) Data
PIVOT (
COUNT(ProductID)
FOR ProductCategory
IN (
[Bikes],[Components],[Clothing],[Accessories]
)
) PivotTable
As you can see, the pivot table field for columns are explicitly stated in the tsql pivot table query.
If you have a lot of pivot table header columns (pivot table field) then it is easier to work with tsql dynamic pivot table query.
DECLARE @PivotTableField nvarchar(max)
SELECT @PivotTableField =
STUFF(
(
SELECT
',[' + Name + ']'
FROM Production.ProductCategory
FOR XML PATH('')
),
1,1,'')
--SELECT @PivotTableField
DECLARE @SQL nvarchar(max)
SELECT @SQL = N'
SELECT
*
FROM (
SELECT
PC.Name ProductCategory,
P.ProductID
FROM Production.Product P
INNER JOIN Production.ProductSubcategory SC
ON SC.ProductSubcategoryID = P.ProductSubcategoryID
INNER JOIN Production.ProductCategory PC
ON PC.ProductCategoryID = SC.ProductCategoryID
) Data
PIVOT (
COUNT(ProductID)
FOR ProductCategory
IN (
' + @PivotTableField + '
)
) PivotTable
'
EXEC sp_ExecuteSQL @SQL
And the output of the above tsql dynamic pivot table is same as the first t-sql pivot table query example.
Actually the only difference between two sql pivot tables is how the pivot table fields are prepared.
In the first example, sql developer explicity stated and defines the pivot table fields, the column headers of the select query.
On the other hand, in dynamic pivot table query, the pivot table fields are defined and concatenated using a select query and passed to the pivot example as a part of the query text.
If you un-comment the "SELECT @PivotTableField" select statement, you will see that the @PivotTableField variable contains the sql concatenate values of product category names : "[Accessories],[Bikes],[Clothing],[Components]"
T-SQL Pivot Table and SQL Dynamic Pivot Table Query in SQL Server 2008 and SQL 2005
For further reading and t-sql examples on sql pivot table in SQL Server 2005 and pivot table in SQL Server 2008, please refer to the following sql tutorials.
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