Creating a Dynamic Crosstab Query

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