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






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