I recently worked on a report for a client in which a date input parameter was used to determine the end dates of the previous four calendar quarters. Each time the report ran, it returned data for all four of those quarters.
I ended up using PIVOT in the course of the stored procedure for the report. The first time I did so, I hard-coded the four quarter end dates I needed to work with. This worked fine for my testing, but I needed the PIVOT to use whatever four quarters end dates were derived from the input parameter. I was fortunate that I knew ahead of time that I would always be pivoting on four and only four date values.
With that consistency in mind, I was able to create a common tables expression with a derived column to consistently “alias” the first quarter as 1, the second quarter as 2, and so forth.
Here is the breakdown of how this works using data on the dining habits of Hobbits. In this example, the report involved requires looking at the first three days of the month. This provides the consistency required to make this particular technique work. It could be possible to modify this procedure to work on value with less consistency but I have not yet tried it.
As you can see from this pivot tables above, Hobbits eat often. The hard coded date values form the column headers. This is nice and all but requires a code change to report on a different set of dates.
Here we see how you can make a CTE with a derived column to pass in to the PIVOT operator. This allows the actual PIVOT to remain exactly the same while reporting on different dates.
Just to make the comparison easier, here are the hard coded January PIVOT results again.
If you set @MealMonth = 2 and run the PIVOT again, you will get the February results:
Again, for ease of comparison, here are the hard coded February results again.
As you can see above, the “dynamic” PIVOT returns the exact same values as the hard coded versions but offers the flexibility of reporting on different periods without changing code. You could just reference a parameter in the CTE instead of a variable as I did.