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

I'm sure I'm missing something here.

我确定我在这里遗漏了一些东西。

I have a dataset like this:

我有这样的数据集:

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

I'd like to query the top 3 results and Pivot them as columns, so the end result set looks like this:

我想查询前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

How can I accomplish this in SQL Server 2005?

如何在SQL Server 2005中完成此操作?

I have been attempting this using PIVOT, but I am still very unfamiliar with that keyword and cannot get it to work the way I want.

我一直在尝试使用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], [2], [3]),
    MAX(T.Type) FOR RowNumber IN ([1], [2], [3]),
    MAX(T.Status) FOR RowNumber IN ([1], [2], [3])
) AS PivotTable;

My actual data set is a bit more complex than this, and I need the top 10 records, not the top 3, so I don't want to simply do CASE WHEN RowNumber = X THEN... for each one.

我的实际数据集比这复杂一点,我需要前10个记录,而不是前3个,所以我不想简单地为每个记录做一个RowNumber = X THEN ...

Update

I tested all the answers below, and found most of them seem about the same with no apparent performance difference in smaller data sets (around 3k records), however there was a slight difference when running the queries against larger data sets.

我测试了下面的所有答案,发现它们中的大多数看起来大致相同,在较小的数据集(大约3k记录)中没有明显的性能差异,但是在针对较大的数据集运行查询时存在细微差别。

Here are the results of my tests using 80,000 records and querying for 5 columns in the top 10 rows, so my end result set was 50 columns + the Id column. I'd suggest you test them on your own to decide which one works best for you and your environment.

以下是我使用80,000条记录并查询前10行中5列的测试结果,因此我的最终结果集为50列+ Id列。我建议你自己测试一下,以决定哪一种最适合你和你的环境。

• bluefoot's answer of unpivoting and re-pivoting the data averaged the fastest at about 12 seconds. I also liked this answer because I found it easiest to read and maintain.

  bluefoot的解锁和重新转动数据的答案平均最快,大约12秒。我也很喜欢这个答案,因为我觉得最容易阅读和维护。

• Aaron's answer and koderoid's answer both suggest using a MAX(CASE WHEN RowNumber = X THEN ...), and was close behind averaging at around 13 seconds.

  Aaron的回答和koderoid的答案都建议使用MAX(例如RowNumber = X THEN ......),并且在13秒左右平均落后于平均值。

• Rodney's answer of using multiple PIVOT statements averaged around 16 seconds, although it might be faster with fewer PIVOT statements (my tests had 5).

  Rodney使用多个PIVOT语句的答案平均约为16秒,尽管PIVOT语句较少可能会更快(我的测试有5个)。

• And the first half of Aaron's answer that suggested using a CTE and OUTER APPLY was the slowest. I don't know how long it would take to run because I cancelled it after 2 minutes, and that was with around 3k records, 3 rows, and 3 columns instead of 80k records, 10 rows, and 5 columns.

  而Aaron建议使用CTE和OUTER APPLY的答案的前半部分是最慢的。我不知道运行需要多长时间,因为我在2分钟后取消了它,那是大约3k记录,3行和3列而不是80k记录,10行和5列。

5 个解决方案

#1

You can do an UNPIVOT and then a PIVOT of the data. this can be done either statically or dynamically:

您可以执行UNPIVOT,然后执行PIVOT数据。这可以静态地或动态地完成:

Static Version:

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

see SQL Fiddle with demo

看看SQL Fiddle with demo

Dynamic Version, this will get the list of columns to unpivot and then to pivot at run-time:

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

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, 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)') 
        ,1,1,'')


set @query 
  = 'select *
      from
      (
        select fk, col + cast(rownumber as varchar(10)) new_col,
          val
        from 
        (
          select fk, rownumber, value, cast(type as varchar(10)) type,
            status
          from yourtable
        ) x
        unpivot
        (
          val
          for col in ('+ @colsunpivot +')
        ) u
      ) x1
      pivot
      (
        max(val)
        for new_col in
          ('+ @colspivot +')
      ) p'

exec(@query)

see SQL Fiddle with Demo

看看SQL Fiddle with Demo

Both will generate the same results, however the dynamic is great if you do not know the number of columns ahead of time.

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

The Dynamic version is working under the assumption that the rownumber is already a part of the dataset.

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

#2

You can try to do the pivot in three separate pivot statements. Please give this a try:

您可以尝试在三个单独的pivot语句中执行pivot。请尝试一下:

SELECT Id
    ,MAX(S1) [Status 1]
    ,MAX(T1) [Type1]
    ,MAX(V1) [Value1]
    --, Add other columns
FROM
(
    SELECT Id, Value , Type, Status
    , 'S' + CAST(ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Status, Type) AS VARCHAR(10)) [Status_RowNumber]
    , 'T' + CAST(ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Status, Type) AS VARCHAR(10)) [Type_RowNumber]
    , 'V' + CAST(ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Status, Type) AS VARCHAR(10)) [Value_RowNumber]
    FROM MyTable
) as T
PIVOT
(   
    MAX(Status) FOR Status_RowNumber IN ([S1], [S2], [S3],[S4],[S5],[S6],[S7],[S8],[S9],[S10])
)AS StatusPivot
PIVOT(
    MAX(Type) FOR Type_RowNumber IN ([T1], [T2], [T3],[T4],[T5],[T6],[T7],[T8],[T9],[T10])
)AS Type_Pivot
PIVOT(
    MAX(Value) FOR Value_RowNumber IN ([V1], [V2], [V3],[V4],[V5],[V6],[V7],[V8],[V9],[V10])
)AS Value_Pivot
GROUP BY Id

I don't know the full scope of the criteria for selecting the top ten records, but this produces and output that may get you closer to your answer.

我不知道选择前十条记录的标准的全部范围,但这会产生和输出,可能会让您更接近您的答案。

SQL Fiddle Example

SQL小提琴示例

#3

Rodney's muli-pivot is clever, that's for sure. Here are two other alternatives that are of course less appealing when you get into the 10X vs. 3X area.

Rodney的muli-pivot是聪明的,这是肯定的。当你进入10X对3X区域时,这里有两个其他选择当然不那么有吸引力。

;WITH a AS
(
    SELECT Id, Value, Type, Status, 
      n = ROW_NUMBER() OVER (PARTITION BY Id ORDER BY [Status], [Type])
    FROM dbo.MyTable
)
SELECT a.Id, 
 Value1 = a.Value, Type1 = a.[Type], Status1 = a.[Status],
 Value2 = b.Value, Type2 = b.[Type], Status2 = b.[Status],
 Value3 = c.Value, Type3 = c.[Type], Status3 = c.[Status]
FROM a
OUTER APPLY (SELECT * FROM a AS T2 WHERE n = a.n + 1 AND id = a.id) AS b
OUTER APPLY (SELECT * FROM a AS T2 WHERE n = b.n + 1 AND id = b.id) AS c
WHERE a.n = 1
ORDER BY a.Id;

-- or --

- 要么 -

;WITH a AS
(
    SELECT Id, Value, [Type], [Status], 
      n = ROW_NUMBER() OVER (PARTITION BY Id ORDER BY [Status], [Type])
    FROM dbo.MyTable
)
SELECT Id,
  Value1  = MAX(CASE WHEN n = 1 THEN Value    END),
  Type1   = MAX(CASE WHEN n = 1 THEN [Type]   END),
  Status1 = MAX(CASE WHEN n = 1 THEN [Status] END),
  Value2  = MAX(CASE WHEN n = 2 THEN Value    END),
  Type2   = MAX(CASE WHEN n = 2 THEN [Type]   END),
  Status2 = MAX(CASE WHEN n = 2 THEN [Status] END),
  Value3  = MAX(CASE WHEN n = 3 THEN Value    END),
  Type3   = MAX(CASE WHEN n = 3 THEN [Type]   END),
  Status3 = MAX(CASE WHEN n = 3 THEN [Status] END)
FROM a
GROUP BY Id
ORDER BY a.Id;

#4

This might work for you, though it's not elegant.

这可能适合你,虽然它不优雅。

select aa.FK_Id
    , isnull(max(aa.Value1), '') as Value1
    , isnull(max(aa.Type1), '') as Type1
    , isnull(max(aa.Status1), '') as Status1
    , isnull(max(aa.Value2), '') as Value2
    , isnull(max(aa.Type2), '') as Type2
    , isnull(max(aa.Status2), '') as Status2
    , isnull(max(aa.Value3), '') as Value3
    , isnull(max(aa.Type3), '') as Type3
    , isnull(max(aa.Status3), '') as Status3
from
(       
    select FK_Id
            , case when RowNumber = 1 then Value else null end as Value1
            , case when RowNumber = 1 then [Type] else null end as Type1
            , case when RowNumber = 1 then [Status] else null end as Status1
            , case when RowNumber = 2 then Value else null end as Value2
            , case when RowNumber = 2 then [Type] else null end as Type2
            , case when RowNumber = 2 then [Status] else null end as Status2
            , case when RowNumber = 3 then Value else null end as Value3
            , case when RowNumber = 3 then [Type] else null end as Type3
            , case when RowNumber = 3 then [Status] else null end as Status3
    from Table1
) aa
group by aa.FK_Id

#5

try something like this:

尝试这样的事情:

declare @rowCount int 
set @rowCount = 10

declare @isNullClause varchar(4024)
set @isnullClause = ''
declare @caseClause varchar(4024)
set @caseClause = ''

declare @i int 
set @i = 1

while(@i <= @rowCount) begin 
    set @isnullClause = @isNullClause + 
                        ' , max(aa.Value' + CAST(@i as varchar(3)) + ') as Value'    + CAST(@i as varchar(3)) +
                        ' , max(aa.Type' + CAST(@i as varchar(3)) + ') as Type'  + CAST(@i as varchar(3)) +
                        ' , max(aa.Status' + CAST(@i as varchar(3)) + ') as Status'  + CAST(@i as varchar(3)) + ' '; 
    set @caseClause = @caseClause + 
        ' , case when RowNumber = ' + CAST(@i as varchar(3)) + ' then Value else null end as Value' + CAST(@i as varchar(3)) +
        ' , case when RowNumber = ' + CAST(@i as varchar(3)) + ' then Type else null end as Type' + CAST(@i as varchar(3)) +
        ' , case when RowNumber = ' + CAST(@i as varchar(3)) + ' then Status else null end as Status' + CAST(@i as varchar(3)) + ' '


    set @i = @i + 1; 
end

declare @sql nvarchar(4000)
set @sql = 'select aa.FK_Id ' + @isnullClause + ' from ( select FK_Id ' 
            + @caseClause + '  from Table1) aa group by aa.FK_Id '

exec SP_EXECUTESQL @sql