Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Creating a Dynamic Crosstab Query

By Cade Bryant,

Crosstab queries (also called "pivot tables") in which you know beforehand the number of column values to aggregate by, can easily be performed in T-SQL using CASE statements wrapped in the SUM function.  Where things get tricky, however, is when you don't know how many aggregation values exist, and you are required to write a query that will dynamically generate as many aggregation columns as there are aggregation values in the database.

The script I wrote below generates a dynamic crosstab report.  It performs quite efficiently, without needing to resort to cursors or temp tables, by generating and executing dynamic SQL.  The only catch is that the code generated cannot exceed 8000 characters.  (If you need more space for your generated code, you could re-write this script so that it inserts the generated code into a table rather than into a variable).

Total article views: 2607 | Views in the last 30 days: 7
Related Articles

dynamic database generation

dynamic database generation


Write Query Dynamic Generated Column

Write Query Dynamic Generated Column


Dynamically generate node names

How can I generate node names dynamically using FOR XML query?


Dynamic Select Generator

Query to generate a dynamic Select statement from any table for moving data between servers.


How to dynamically change Aggregate function in Cube ?

How to dynamically change Aggregate function in Cube ?


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones