Jeff Moden (7/1/2013)
N.B. (7/1/2013)
I ended up going the Copy/Paste route and iterating every possible number in order to get this working - it's ugly, but it does work.The article that you were provided a link for on dynamic crosstabs could have saved you a lot of time. It has a technique for how to have the code write code instead of doing all that CPR (Copy/Past/Replace).
Here it is again. You really should read it because the methods used have application in a whole lot of places.
Hi Jeff,
I did read through it, but I was having difficulty applying it to the situation I'm in - the thing is, there really is nothing dynamic about this - there are no dates, there are no parameters that may get passed to the select - it just IS. It runs on a weekly basis to update a table that we can then use in other reports to provide a more compact way of retrieving the data.
The "dynamic" part is that instead of having a huge chunk of code for two columns that get repeated 60 times, I was trying to find a solution that would essentially do a do/while loop - in other words, while i < 61, do this, i+1, repeat.
I've re-read the article in question, as well as using Luis' solution as a base, and while the SQL that it generate in the PRINT statement looks correct, it doesn't actually execute correctly, because, as I said, it's unable to find the primary CTE after it's used in the CTE to generate the numbers used later in the script.