我确定我在这里遗漏了一些东西.
我有这样的数据集：
FK RowNumber Value Type Status 1 1 aaaaa A New 1 2 bbbbb B Good 1 3 ccccc A Bad 1 4 ddddd C Good 1 5 eeeee B Good 2 1 fffff C Bad 2 2 ggggg A New 2 3 hhhhh C Bad 3 1 iiiii A Good 3 2 jjjjj A Good
我想查询前3个结果并将它们作为列进行透视,因此最终结果集如下所示：
FK Value1 Type1 Status1 Value2 Type2 Status2 Value3 Type3 Status3 1 aaaaa A New bbbbb B Good ccccc A Bad 2 fffff C Bad ggggg A New hhhhh C Bad 3 iiiii A Good jjjjj A Good
如何在sql Server 2005中完成此操作？
我一直在尝试使用PIVOT,但我仍然非常不熟悉该关键字,无法让它以我想要的方式工作.
SELECT * --Id,[1],[2],[3]
FROM
(
SELECT Id,Value,Type,Status,ROW_NUMBER() OVER (PARTITION BY Id ORDER Status,Type) as [RowNumber]
FROM MyTable
) as T
PIVOT
(
-- I know this section doesn't work. I'm still trying to figure out PIVOT
MAX(T.Value) FOR RowNumber IN ([1],[3]),MAX(T.Type) FOR RowNumber IN ([1],MAX(T.Status) FOR RowNumber IN ([1],[3])
) AS PivotTable;
我的实际数据集比这复杂一点,我需要前10个记录,而不是前3个,所以我不想简单地为每个记录做一个RowNumber = X THEN …
更新
我测试了下面的所有答案,发现它们中的大多数看起来大致相同,在较小的数据集(大约3k记录)中没有明显的性能差异,但是在针对较大的数据集运行查询时存在细微差别.
以下是我使用80,000条记录并查询前10行中5列的测试结果,因此我的最终结果集是Id列的50列.我建议你自己测试一下,以决定哪一种最适合你和你的环境.
> bluefoot’s answer的数据平移和重新转动平均速度最快,大约12秒.我也很喜欢这个答案,因为我觉得最容易阅读和维护.
> Aaron’s answer和koderoid’s answer都建议使用MAX(例如RowNumber = X THEN ……),并且在13秒左右平均落后于平均值.
> Rodney’s answer使用多个PIVOT语句平均大约16秒,尽管使用更少的PIVOT语句可能会更快(我的测试有5个).
>并且建议使用CTE和OUTER APPLY的Aaron’s answer的上半部分是最慢的.我不知道运行需要多长时间,因为我在2分钟后取消了它,那是大约3k记录,3行和3列而不是80k记录,10行和5列.
解决方法
您可以执行UNPIVOT,然后执行PIVOT数据.这可以静态地或动态地完成：
静态版本：
select *
from
(
select fk,col + cast(rownumber as varchar(1)) new_col,val
from
(
select fk,rownumber,value,cast(type as varchar(10)) type,status
from yourtable
) x
unpivot
(
val
for col in (value,type,status)
) u
) x1
pivot
(
max(val)
for new_col in
([value1],[type1],[status1],[value2],[type2],[status2],[value3],[type3])
) p
动态版本,这将获取要拆分的列列表,然后在运行时进行透视：
DECLARE @colsUnpivot AS NVARCHAR(MAX),@query AS NVARCHAR(MAX),@colsPivot as NVARCHAR(MAX)
select @colsUnpivot = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id('yourtable') and
C.name not in ('fk','rownumber')
for xml path('')),1,'')
select @colsPivot = STUFF((SELECT ','
+ quotename(c.name
+ cast(t.rownumber as varchar(10)))
from yourtable t
cross apply
sys.columns as C
where C.object_id = object_id('yourtable') and
C.name not in ('fk','rownumber')
group by c.name,t.rownumber
order by t.rownumber
FOR XML PATH(''),TYPE
).value('.','NVARCHAR(MAX)'),'')
set @query
= 'select *
from
(
select fk,col + cast(rownumber as varchar(10)) new_col,val
from
(
select fk,status
from yourtable
) x
unpivot
(
val
for col in ('+ @colsunpivot +')
) u
) x1
pivot
(
max(val)
for new_col in
('+ @colspivot +')
) p'
exec(@query)
两者都会生成相同的结果,但如果您不提前知道列数,则动态很好.
动态版本在假设rownumber已经是数据集的一部分的情况下工作.
