无聚集旋转

Question

我正在尝试枢轴不聚合，并撞上了一点墙。

下面是我想要开始工作的the示例：

declare @optionalFields table (ParentId int, Name nvarchar(50), Value nvarchar(50));
insert into @optionalFields values (1, 'Field1', 'Foo');
insert into @optionalFields values (1, 'Field2', 'Bar');
insert into @optionalFields values (1, 'Field3', '42');
insert into @optionalFields values (2, 'Field1', 'Bar');
insert into @optionalFields values (2, 'Field2', 'Foo');
insert into @optionalFields values (2, 'Field3', '24');

declare @data table (Id int, Name nvarchar(50));
insert into @data values (1, 'Test record 1');
insert into @data values (2, 'Test record 2');

declare @joined table (Id int, Name nvarchar(50), OptionalFieldName nvarchar(50), OptionalFieldValue nvarchar(50));
insert into @joined
select 
     data.Id
    ,data.Name
    ,opt.Name
    ,opt.Value
from @data data
    inner join @optionalFields opt on data.Id = opt.ParentId

declare @cols as nvarchar(max) = 
    stuff((select distinct ',' + quotename(OptionalFieldName) from @joined for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '');

select * into #tmp from @joined
-- just to see that it's returning the expected values (it does)
select
     Id
    ,Name
    ,OptionalFieldName
    ,OptionalFieldValue
    ,row_number() over (partition by Id order by Id) RN 
from #tmp -- this is the FROM clause in the below dynamic-sql query

declare @query as nvarchar(max) = '
    select Id, Name, ' + @cols + '
    from (select Id, Name, OptionalFieldName, OptionalFieldValue, row_number() over (partition by Id order by Id) RN from #tmp) src 
    pivot (max(OptionalFieldName) for RN in (' + @cols + ')) pvt';

execute(@query);
drop table #tmp;

SSMS给了我两个错误：

• Msg 8114，级别16，状态1，第4行将数据类型nvarchar转换为bigint错误。
• Msg 473，16级，状态1，第4行不正确的值"Field1“是在PIVOT操作符中提供的。

“调试”select语句返回以下内容：

​

​

这篇文章(上面的链接)似乎很有希望，但我似乎无法让它发挥作用。我做错了什么？或者这篇文章是完全错误的，而我想要做的是不可能的？

我看到了许多类似的这样的问题，但它们要么涉及所有--可以“只使用”聚合的数字字段，或者涉及可以作为简单联接实现的已知列--我不知道要选择哪些OptionalFieldName值，而OptionalFieldValue值是根本无法聚合的字符串，至少是AFAIK。

Answer 1

我有点搞不懂你为什么要用row_number()来欺骗它。即使您有字符串值，仍然可以聚合它--您只需要使用max或min来获得结果。

我总是建议先用硬编码的值编写查询，特别是在尝试使用动态SQL之前使用透视时。我不知道你为什么不能这样写查询：

select Id, Name, Field1, Field2, Field3
from 
(
  select
     Id
    ,Name
    ,OptionalFieldName
    ,OptionalFieldValue
  from #tmp
) d
pivot
(
  max(OptionalFieldValue)
  for OptionalFieldName in (Field1, Field2, Field3)
) piv;

看演示。

如果您真的需要动态SQL，只需编写它：

declare @optionalFields table (ParentId int, Name nvarchar(50), Value nvarchar(50));
insert into @optionalFields values (1, 'Field1', 'Foo');
insert into @optionalFields values (1, 'Field2', 'Bar');
insert into @optionalFields values (1, 'Field3', '42');
insert into @optionalFields values (2, 'Field1', 'Bar');
insert into @optionalFields values (2, 'Field2', 'Foo');
insert into @optionalFields values (2, 'Field3', '24');

declare @data table (Id int, Name nvarchar(50));
insert into @data values (1, 'Test record 1');
insert into @data values (2, 'Test record 2');

declare @joined table (Id int, Name nvarchar(50), OptionalFieldName nvarchar(50), OptionalFieldValue nvarchar(50));
insert into @joined
select 
     data.Id
    ,data.Name
    ,opt.Name
    ,opt.Value
from @data data
    inner join @optionalFields opt on data.Id = opt.ParentId

declare @cols as nvarchar(max);
set @cols = stuff((select distinct ',' + quotename(OptionalFieldName) 
                   from @joined
                   for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '');

select * into #tmp from @joined

DECLARE @query  AS NVARCHAR(MAX)

set @query = 'SELECT Id, Name,' + @cols + ' 
            from 
             (
                select Id
                  ,Name
                  ,OptionalFieldName
                  ,OptionalFieldValue
                from #tmp
            ) x
            pivot 
            (
                max(OptionalFieldValue)
                for OptionalFieldName in (' + @cols + ')
            ) p '

execute(@query);

见Demo。这两个版本似乎都提供了您所请求的结果。

Answer 2

编辑:这个答案在这里只是为了展示另一种方式来完成这个旋转。答案来自“蓝脚”是最好的解决方案！

希望我理解你需要的是正确的：

declare @optionalFields table (ParentId int, Name nvarchar(50), Value nvarchar(50));
insert into @optionalFields values (1, 'Field1', 'Foo');
insert into @optionalFields values (1, 'Field2', 'Bar');
insert into @optionalFields values (1, 'Field3', '42');
insert into @optionalFields values (2, 'Field1', 'Bar');
insert into @optionalFields values (2, 'Field2', 'Foo');
insert into @optionalFields values (2, 'Field3', '24');

declare @data table (Id int, Name nvarchar(50));
insert into @data values (1, 'Test record 1');
insert into @data values (2, 'Test record 2');


select 
     data.Id
    ,data.Name
    ,opt.Name as Name1
    ,opt.Value into #tmp
from @data data
    inner join @optionalFields opt on data.Id = opt.ParentId

declare @cols as nvarchar(max) = 
    stuff((select distinct ',' + quotename(Name1) from #tmp for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '');

DECLARE @cols1 as nvarchar(max) = 
    stuff((select distinct +',MAX(CASE WHEN (pvt1.'+quotename(Name1) +' = ros.RN AND pvt1.id = ros.id) THEN ros.Value ELSE NULL END) as '+quotename(Name1)  from #tmp for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '');

declare @query as nvarchar(max) = '
 ;WITH cte AS(
 SELECT * FROM #tmp
),

        ros AS (
        SELECT ROW_NUMBER() OVER (partition by Id order by Id) AS [RN],id,Value
        FROM cte),
        pvt1 AS (
    select *
    from (select Id, Name, Name1, row_number() over (partition by Id order by Id) RN 
            from cte) src 
    pivot (max(RN) for Name1 in ('+@cols+')) pvt)

    SELECT pvt1.ID,
            pvt1.Name,
            '+@cols1+'
    FROM pvt1 
    CROSS JOIN ros
    GROUP BY pvt1.ID,
            pvt1.Name'

execute(@query);
drop table #tmp

结果：

ID  Name            Field1  Field2  Field3
1   Test record 1   Foo     Bar     42
2   Test record 2   Bar     Foo     24

如果添加更多像insert into @optionalFields values (2, 'Field4', '15');这样的字段，您将得到：

ID  Name            Field1  Field2  Field3  Field4
1   Test record 1   Foo     Bar     42      NULL
2   Test record 2   Bar     Foo     24      15