Fantastic article, i have to create a Dynamic cross tabbed report, where the column tabs are each week of a year not monthly!! 🙂 !!!!this article is exactly what i was looking for, something that creates those dynamic cross tabs without ANY cursors, temp tables or global temp tables...
I was able to create the weekly tabs with some minor tweaking to your code ...but i must say in addition to learning how to create a cross tabs on the fly , i picked up a few other pointers-
1] Creating the 'test' table and tally table- something that i will use to create test data in future..
2] Some useful pointers for query performance, like using a select to assign a variable..
3] Using the cube and rollup functions
4]Using a select and coalesce to concatenate multiple columns
But most importantly- how to break up the problem statement into little chunks--- i.e. creating the 'static' portion first and then 'converting ' into the dynamic portion using variables...
much appreciated and i hope to keep learning more..!!!!