T-SQL Dynamic Pivot with case-sensitive column names

Question

I'm working on a query for SQL Server (2008 R2 at the moment) - my goal is to produce a result set that lists every report defined in SSRS under a specific path along with a grid that has a column for each uniquely named report parameter on the server, with the contents of the grid being a "checkmark" (e.g. non-null value) for each Report + Parameter combination for which the corresponding report has a parameter with the corresponding name. The query needs to be case sensitive on report parameter names - one purpose of the query is to identify reports that have parameters spelled with inconsistent casing.

I was able to write that query using a number of techniques (that some might call ugly hacks):

use ReportServer
go

declare @path nvarchar(255);
set @path = N'SSRS Path To Folder'

-- return a table with two columns: ReportName, ParameterName with one row for each
-- distinct ReportName + ParameterName combination
select
  t.Name as ReportName,
  pn.value collate Latin1_General_CS_AI as ParameterName
into
  #rp
from
  (
    -- return a table with two columns: ReportName and ParameterNames (comma-separated list of
    -- parameters in declaration order)
    select
      [Name],
      (select STUFF((select ', ' + p.n.value('.', 'varchar(255)') 
        from ParameterXml.nodes('/Parameters/Parameter/Name') p(n) 
        for xml path('')), 1, 2, '')
      ) as ParameterNames
    from
    (
      select
        *,
        CAST(Parameter as xml) as ParameterXml
      from
        [Catalog] 
    ) c
    where
      [Path] like '/' + @path + '/%'
      and [Type] = 2
  ) t
  cross apply dbo.SplitString(t.ParameterNames) pn

-- Pivot the above result into a table with one row per report and one column for each
-- distinct report parameter name.  Parameter-named columns contain a flag - 1 or null - 
-- that indicates whether the report corresponding to that row defines the parameter 
-- corresponding to that column.
create database CS_Temp collate Latin1_General_CS_AI;
go

use CS_Temp
go

declare @cols nvarchar(MAX), @query nvarchar(MAX);
set @cols = STUFF(
            (
              select 
                distinct ','+QUOTENAME(rp.ParameterName) 
              from 
                #rp rp
              for xml path(''), type).value('.', 'nvarchar(max)'
            ),1,1,''
          );

set @query = 'SELECT ReportName, ' + @cols + ' from 
  (
      select ReportName, 1 as Used, ParameterName from #rp
  ) x
  pivot 
  (
      max(Used) for ParameterName in (' + @cols + ')
  ) p
';

execute(@query)
go

drop table #rp

use ReportServer;
go

drop database CS_Temp;
go

(SplitString function from Erland Sommarskog/Itzik Ben-Gan, dynamic pivot technique from Aaron Bertrand). This query does work, but it's slow and ugly - which is actually OK for my use case. What I'm wondering though, is if there's any better way to get the pivot to work with case sensitive column names than what I've done here: Actually creating a database with a case-sensitive collation, switching to that context and executing the pivot query. The database serves no purpose other than providing the collation for the database meta-data - i.e. column names in the result of the pivot query.

Answer 1

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.