T-SQL Dynamic Pivot Table Examples for SQL Server 2008
This SQL tutorial shows T-SQL dynamic pivot table query example which can be easily run on MS SQL Server 2008 AdventureWorks sample database.
First we will begin to dynamic pivot table tutorial with the standard pivot table syntax and an example using this pivot table syntax.
Note that the pivot table columns that are the state codes are limited in numbers and defined static within the SQL Synamic Pivot Table query.
select
*
from (
select
A.AddressID,
S.StateProvinceCode
from Person.Address A
inner join Person.StateProvince S
on s.StateProvinceID = A.StateProvinceID
) Data
PIVOT (
Count(AddressID)
FOR StateProvinceCode
IN (
[NE],[WA],[MS],[HE],[CT],[WY]
)
) PivotTable
SQL developers have to change static string "[NE],[WA],[MS],[HE],[CT],[WY]" which forms the pivot table column names into a dynamic structure using sql dynamic query rules.
Defining SQL pivot table columns static within the tsql dynamic pivot table query is not preferred by sql developers.
In real world, you have no chance to define the column names while coding the t-sql pivot table query.
So SQL developers and SQL Server database administrators require dynamic pivot table queries.
Now you will see how we can build t-sql pivot table select query step by step in the following section.
DECLARE @States nvarchar(max)
SELECT @States =
STUFF(
(
select distinct ',[' + S.StateProvinceCode + ']'
from Person.Address A
inner join Person.StateProvince S
on s.StateProvinceID = A.StateProvinceID
for xml path('')
),
1,1,'')
DECLARE @SQL nvarchar(max)
SELECT @SQL = N'
select
*
from (
select
A.AddressID,
S.StateProvinceCode
from Person.Address A
inner join Person.StateProvince S
on s.StateProvinceID = A.StateProvinceID
) Data
PIVOT (
Count(AddressID)
FOR StateProvinceCode
IN (
' + @States + '
)
) PivotTable
'
exec sp_executesql @SQL
To sum up, let's now summarize and identify the steps of the above T-SQL dynamic pivot table query.
1) Define a nvarchar sql data type variable.
2) SQL concatenate pivot table column names using FOR XML PATH() sql string concatenation method.
3) Define a nvarchar sql data type variable for building sql select query.
4) Build query by using Pivot Table syntax and concatenated sql string values
5) Execute dynamic sql query by using sp_executesql stored procedure
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