T-SQL Dynamic Pivot with case-sensitive column names
To use the PIVOT command you need to have a case sensitive collation to have case sensitive columns, as you've found. I like the cunningness of a new temporary CS db BUT there's a couple of other approaches I can think of that don't require it:
- do all this in a report! not in SQL. Easier! But not really answering the question
- instead of using PIVOT do it old-style with a separate column in your query per Parameter, like this https://stackoverflow.com/a/5799709/8479. You can generate the dynamic SQL yourself, so it's not so tedious. The great thing about this is it's only the CASE statement comparisons that need be case sensitive, which is data and therefore uses the collation of the table (or sub query). You never refer to the column names after the data is output, they're just column aliases, so it's fine if there are several the same (according to the db collation).
- instead of just using the parameter names as column names, include some parameter number prefix or suffix, like 01_myParam, 02_MyParam, 03_yourparam. You'll compute the prefix in a subquery and again it's a data comparison therefore doesn't need case sensitive columns. When the columns are used in the PIVOT statement the numerical prefix/suffix means case sensitivity isn't required. Clearly the downside is you have an annoying number in the column name of course :) if you really cared you could use a non-visible character in the column names to differentiate between multiple otherwise-identical column names, e.g. "myParam", "MyParam ", "myparam ", only suffixing the ones that have a duplicate name and using STUFF to add multiple chars or have a subquery with a table of non printing chars that you index into.