sql – 如何将行数据作为列查询？

我有这样的数据集：

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列.

解决方法

静态版本：

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

见SQL Fiddle with demo

动态版本,这将获取要拆分的列列表,然后在运行时进行透视：

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)

见SQL Fiddle with Demo

两者都会生成相同的结果,但如果您不提前知道列数,则动态很好.

动态版本在假设rownumber已经是数据集的一部分的情况下工作.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!