将行转置/旋转到列 [英] Transpose / Pivot rows to columns

问题描述

我正在尝试转置(枢纽?)一张桌子.这是我当前的设置.
当前表:

I am trying to transpose (Pivot?) a table. This is my current setup.
Current Table:

ID | Value
1  | 10
1  | 11
1  | 12
1  | 13
1  | 14
2  | 123
3  | 13423
3  | 1134
3  | 1234

寻求以下结果:

ID | Value01 | Value 02 | Value 03 | Value 04 | Value 05
1  |  10     |  11      |   12     |   13     |  14
2  |  123
3  | 13423   | 1134     | 1234

当前，我正在尝试使用PIVOT，但是我不确定如何在没有类别列"(例如几天或几个月)的情况下如何使用PIVOT.我可以使用ID列吗?

Currently I am trying it with PIVOT however I am not completely sure how to PIVOT without a "category column" (such as days, or months). Could I use the ID column for this?

SELECT ID, Value, [0], [1], [2], [3], [4] 
FROM (
      SELECT ID, Value FROM dbo.TABLE
) SourceTable 
PIVOT (
      VALUE FOR ID IN ([0], [1], [2], [3], [4])
) AS PivotTable

每个ID都没有预设的VALUE数量.但是，如果需要一个已知数字，则5个值(因此5列)就足够了.

There is no preset amount of VALUE's for each ID. But if it is required to have a known number, 5 values (and thus 5 columns) is enough.

推荐答案

您当前的查询已关闭，您缺少要用作新列名的值.我的建议是使用row_number()，它将在每个id上创建一个递增的值，然后您可以使用PIVOT函数为以下每个序列值返回max(value):

Your current query is close, you are missing the value that you want as the new column names. My suggestion would be to use row_number() which will create an incremented value over each id, then you can use the PIVOT function to return the max(value) for each of these sequenced values:

SELECT ID, [0], [1], [2], [3], [4] 
FROM 
(
  SELECT ID, Value, 
    row_number() over(partition by id 
                        order by id) -1  seq
  FROM yourtable
) SourceTable 
PIVOT 
(
  max(VALUE)
  FOR seq IN ([0], [1], [2], [3], [4])
) AS PivotTable;

请参见带有演示的SQL小提琴

这篇关于将行转置/旋转到列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！