具有区分大小写的列名的T动态数据透视

Question

我正在对server进行查询(目前是2008年R2 )--我的目标是生成一个结果集，该结果集列出在特定路径下定义的每个SSRS报表，以及一个网格，该网格为服务器上每个唯一命名的报表参数都有一个列，网格的内容是每个报表+参数组合的“复选标记”(例如，非空值)，对应的报表具有相应名称的参数。查询需要对报表参数名称区分大小写--查询的目的之一是识别具有大小写不一致的参数的报表。

我能够使用许多技术(有些人可能称之为丑陋的黑客)编写该查询：

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函数来自Erland Sommarskog/Itzik Ben-Gan，动态枢轴技术来自Aaron Bertrand)。这个查询确实有效，但是它又慢又丑--这对我的用例来说是可以的。不过，我想知道的是，是否有更好的方法让支点处理区分大小写的列名，而不是我在这里所做的工作:实际创建一个具有区分大小写的排序规则的数据库，切换到该上下文并执行支点查询。数据库除了为数据库元数据提供排序规则之外没有其他用途--即在数据透视查询的结果中提供列名。

Answer 1

要使用枢轴命令，您需要有一个区分大小写的排序规则，以便有区分大小写的列，正如您已经发现的。我喜欢一个新的临时CS db的巧妙之处，但我可以想到一些其他的方法不需要它：

• 把这些都写在报告里！不是在SQL中。容易点！但没有真正回答这个问题
• 不要使用支点，而是使用旧的样式，在每个参数的查询中有一个单独的列，比如这个https://stackoverflow.com/a/5799709/8479。您可以自己生成动态SQL，这样就不会那么乏味了。最重要的是，只需要区分大小写的CASE语句比较，这是数据，因此使用表(或子查询)的排序规则。在输出数据之后，您永远不会引用列名，它们只是列别名，所以如果有几个相同的名称(根据db排序规则)，就可以了。
• 不要仅仅使用参数名作为列名，而是包含一些参数号前缀或后缀，如01_myParam、02_MyParam、03_yourparam。您将在子查询中计算前缀，同样，它是一个数据比较，因此不需要区分大小写的列。当在透视语句中使用列时，数值前缀/后缀意味着不需要区分大小写。当然，这样做的缺点是在列名中有一个恼人的数字:)如果您真的关心的话，您可以在列名中使用一个不可见的字符来区分多个相同的列名，例如"MyParam“、"myparam”、"myParam"，它们只后缀有重复名称的列名，并使用一些东西添加多个字符，或者有一个带有索引到的非打印字符表的子查询。