具有区分大小写的列名的T-SQL动态Pivot

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.

我正在查询SQL Server(目前2008 R2)——我的目标是产生一个结果集列表中定义的每个报告SSRS的在一个特定的路径和一个网格,每个惟一命名的列报告参数在服务器上,与网格作为一个“对号”的内容(如非空值)为每个报告+参数组合对应的报告有一个参数对应的名字。查询需要对报表参数名区分大小写——查询的目的之一是识别具有不一致大小写的参数的报表。

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.

(SplitString函数来自Erland Sommarskog/Itzik Ben-Gan, Aaron Bertrand)。这个查询确实有效，但是它很慢而且很难看——这对于我的用例来说是可以的。但是，我想知道的是，是否有更好的方法来让pivot与case敏感的列名称一起工作，而不是我在这里所做的工作:实际创建一个带有区分大小写排序的数据库，切换到该上下文并执行pivot查询。数据库除了为数据库元数据提供排序之外没有其他用途——例如，在pivot查询的结果中提供列名。

1 个解决方案

#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:

要使用PIVOT命令，您需要有区分大小写的排序，才能有区分大小写的列，正如您所发现的那样。我喜欢一个新的临时的CS db的狡猾，但是我能想到的还有其他一些不需要的方法:

• do all this in a report! not in SQL. Easier! But not really answering the question
• 在报告中完成所有这些!不是在SQL。更容易!但并没有真正回答这个问题。
• instead of using PIVOT do it old-style with a separate column in your query per Parameter, like this https://*.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).
• 不要使用PIVOT，而是在每个参数的查询中使用独立的列，比如这个https://*.com/a/579979/8479。您可以自己生成动态SQL，所以不会太麻烦。最重要的是，只有CASE语句比较需要区分大小写，这是数据，因此使用了表的排序(或子查询)。在数据输出之后，您永远不会引用列名，它们只是列别名，所以如果有几个相同的列名(根据db排序)也可以。
• 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.
• 除了将参数名称用作列名之外，还应该包含一些参数编号前缀或后缀，如01_myParam、02_MyParam、03_yourparam。您将在子查询中计算前缀，这是一个数据比较，因此不需要区分大小写的列。当列在PIVOT语句中使用时，数值前缀/后缀表示不需要区分大小写。显然，缺点是你的列名中有一个烦人的数字当然是:)如果你真的在意，你可以在列名中使用一个不可见的字符来区分多个其他相同的列名，例如。“myParam”、“myParam”、“myParam”只对具有重复名称的字符加上后缀，并使用东西添加多个字符，或者使用一个包含索引的非打印字符表的子查询。