sql-server - 没有聚合或最大值的 SQL 数据透视查询

Question

我试图在没有聚合的情况下获得一个数据透视结果，我尝试了 max 但没有帮助，可能是我做错了什么。

当我运行下面的查询时

declare @t table 
(
    col1 int,
    col2 varchar(100),
    col3 varchar(100),
    col4 varchar(100),
    col5 int
)

insert into @t values(1,'test1','p1','v1',1)
insert into @t values(1,'test1','p2','v2',2)
insert into @t values(1, 'test1','p3','v3',3)
insert into @t values(1,'test1','p1','v11',1)
insert into @t values(1,'test1','p1','v12',1)
insert into @t values(1,'test1','p2','v21',2)
insert into @t values(1,'test1','p2','v22',2)

--select * from @t
select col1,
    col2, 
    [p1],
    [p2],
    [p3]
from 
(
    select * from @t
) x
pivot
(
    Max(col4 )
    for col3 in ([p1],[p2],[p3])
) pvt

我得到以下结果

我正在尝试获得以下结果

如果您能向我展示实现这一目标的途径，那就太好了。

Answer 1

您仍然需要对 PIVOT 使用聚合函数，但是您需要某种值来根据 col1、col2 的组合返回多行和 col3。这是您要使用像 row_number() 这样的窗口函数的地方。

如果您使用以下查询，您应该能够得到结果:

select col1, col2, p1, p2, p3
from 
(
  select col1, col2, col3, col4, 
    rn = row_number() over(partition by col1, col2, col3 order by col5, col4)
  from @t
) d
pivot
(
  max(col4)
  for col3 in (p1, p2, p3)
) p;

参见 SQL Fiddle with Demo

row_number() 函数创建了一个由col1、col2 和col3 划分的唯一序列值 - 然后我按您的 col5 和 col4 值对结果进行排序，以按特定顺序创建序列。当数据透视表对导致返回多行而不是单行的数据进行分组时，将使用此新值。