T-SQL动态透视表与区分大小写的列名

Question

我正在处理一个SQL 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

< p >（Erland Sommarskog/Itzik Ben-Gan的SplitString函数，Aaron Bertrand的动态轴技术）。这个查询确实有效，但速度较慢且不美观 - 对于我的用例来说实际上是可以接受的。我想知道的是，是否有更好的方法使数据透视表按照大小写敏感的列名工作，而不是我在此处所做的：创建具有区分大小写排序规则的数据库，切换到该上下文并执行数据透视查询。该数据库除了为数据库元数据 - 即数据透视查询结果中的列名提供排序规则外没有任何作用。

- CarlDaniel

---

@CarlDaniel，您能否提供一些示例数据、表结构以及最终期望的结果呢？ - Taryn

任何已部署报表的SSRS实例都可以作为样本数据。从2005年开始的任何版本的SSRS都应该可以使用。 - CarlDaniel

我成功地让PIVOT区分了不同大小写的名称，只需在默认为CS排序规则的数据库上下文中运行针对该数据库创建的表即可。我没有尝试在一个CS DB的上下文中运行它，并在另一个CS DB的表上尝试它，但这也可能有效。但是，无论托管数据库的默认排序规则如何，在CI DB的上下文中尝试PIVOT以及在忽略上下文的情况下尝试在CI DB中创建表都失败了。 - Andriy M

如果期望的输出是或可能是SSRS报告，我会在SSRS中通过向Tablix添加列组来进行数据透视。默认情况下，SSRS是大小写不敏感的。 - Mike Honey

Carl，输出不一定要是报告。您还可以创建一个订阅，运行报告并以您所需的格式发送输出。 - Steve Mangiameli

Answer 1

要使用PIVOT命令，您需要有区分大小写的排序规则以使用区分大小写的列，正如您所发现的。我喜欢新建临时CS数据库的机智想法，但我还可以想到另外几种方法不需要它：

• 在报告中完成所有这些操作，不要使用SQL。 更容易！但并没有真正回答问题。
• 不要使用PIVOT，而是在查询中使用每个参数的单独列的旧式方法，例如https://dev59.com/YXVC5IYBdhLWcg3wYQAp#5799709。您可以自己生成动态SQL，因此不会那么繁琐。这样做的好处是仅需要CASE语句比较是大小写敏感的，这是数据，因此使用表格（或子查询）的排序规则。输出数据后，您不再引用列名称，它们只是列别名，因此如果有多个相同的列名（根据数据库排序规则），则没关系。
• 不要只使用参数名称作为列名称，还应包括一些参数编号前缀或后缀，例如01_myParam，02_MyParam，03_yourparam。您将在子查询中计算前缀，因此它是数据比较，因此不需要大小写敏感列。当在PIVOT语句中使用列时，数字前缀/后缀意味着不需要区分大小写。显然，缺点是您在列名中有一个恼人的编号 :) 如果您真的关心，您可以在列名中使用非可见字符来区分多个否则相同的列名，例如“myParam”、“MyParam ”，“myparam ”，仅在有重复名称的列后缀，并使用STUFF添加多个字符或具有非打印字符表的子查询进行索引。

- Rory