I have a large table with transaction data. It can be multiple transactions per month. I have 2 years of data and growing. It all needs to be reported out on. I'm trying to run this monthly, to create a table and allow users to drill through it using a web interface.
I'd really like to roll all of this into 1 table for both years but here are the problems I am running in to.
Maybe I'm biting off more than I can chew, trying to do too much in one query. Any hell with be greatly appreciated!
I've never been a fan of PIVOT in TSQL. Generally I see it used where it should be the presentation layer that does the pivot as part of the formatting. What is your intended UI? If it's SSRS then return all the data efficiently via a procedure and then point a report at the procedure and use a Tablix to pivot the data you want it to.
You could add a year parameter for users to select a specific year or all data but the database does the data access and the SSRS report does the pretty.
To work on with the PIVOT however, you could make the Month column have the year value in it, so:
2011_01, 2011_02 ...You'll need to have the format right for it to be sorted chronologically.
answered Aug 22 '12 at 12:33 PM