将行转换为列

I've searched high and low for an answer to this so apologies if it's already answered! I have the following result from a query in SQL 2005:

我到处寻找这个问题的答案，如果它已经被回答了，我很抱歉!我有以下SQL 2005查询结果:

What I want is

我想要的是

column1|column2|column3|column4|column5
---------------------------------------
1234   |1235   |1236   |1267   |1278

I can't quite get my head around the pivot operator but this looks like it's going to be involved. I can work with there being only 5 rows for now but a bonus would be for it to be dynamic, i.e. can scale to x rows.

我不能完全理解主元算子但是看起来它是有关系的。我现在只能处理5行，但额外的好处是它是动态的，也就是说可以扩展到x行。

EDIT: What I'm ultimately after is assigning the values of each resulting column to variables, e.g.

编辑:我最终要做的是将每个结果列的值分配给变量，例如。

DECLARE @id1 int, @id2 int, @id3 int, @id4 int, @id5 int

SELECT @id1 = column1, @id2 = column2, @id3 = column3, @id4 = column4, 
@id5 = column5 FROM [transposed_table]

3 个解决方案

#1

You also need a value field in your query for each id to aggregate on. Then you can do something like this

您还需要查询中的值字段，以便对每个id进行聚合。然后你可以做这样的事情。

select [1234], [1235]
from
(
    -- replace code below with your query, e.g. select id, value from table
    select
    id = 1234,
    value = 1
    union
    select
    id = 1235,
    value = 2
) a
pivot
(
  avg(value) for id in ([1234], [1235])
) as pvt

#2

I think you'll find the answer in this answer to a slightly different question: Generate "scatter plot" result of members against sets from SQL query

我想你会在这个问题的回答中找到答案:根据SQL查询的集合生成成员的“散点图”结果

The answer uses Dynamic SQL. Check out the last link in mellamokb's answer: http://www.sqlfiddle.com/#!3/c136d/14 where he creates column names from row data.

答案是使用动态SQL。在mellamokb的答案中查看最后一个链接:http://www.sqlfiddle.com/#!3/c136d/14，从行数据创建列名。

#3

In case you have a grouped flat data structure that you want to group transpose, like such:

如果你有一个分组的平面数据结构，你想对转置进行分组，比如:

GRP  | ID
---------------
1    | 1234
1    | 1235
1    | 1236
1    | 1267
1    | 1278
2    | 1234
2    | 1235
2    | 1267
2    | 1289

And you want its group transposition to appear like:

你想让它的组转置看起来像:

GRP | Column 1 | Column 2 | Column 3 | Column 4 | Column 5
-------------------------------------------------------------
1   | 1234     | 1235     | 1236     | 1267     | 1278
2   | 1234     | 1235     | NULL     | 1267     | NULL

You can accomplish it with a query like this:

您可以通过以下查询来完成:

SELECT
    Column1.ID As column1,
    Column2.ID AS column2,
    Column3.ID AS column3,
    Column4.ID AS column4,
    Column5.ID AS column5
FROM
    (SELECT GRP, ID FROM FlatTable WHERE ID = 1234) AS Column1
LEFT OUTER JOIN
    (SELECT GRP, ID FROM FlatTable WHERE ID = 1235) AS Column2
ON Column1.GRP = Column2.GRP
LEFT OUTER JOIN
    (SELECT GRP, ID FROM FlatTable WHERE ID = 1236) AS Column3
ON Column1.GRP = Column3.GRP
LEFT OUTER JOIN
    (SELECT GRP, ID FROM FlatTable WHERE ID = 1267) AS Column4
ON Column1.GRP = Column4.GRP
LEFT OUTER JOIN
    (SELECT GRP, ID FROM FlatTable WHERE ID = 1278) AS Column5
ON Column1.GRP = Column5.GRP

(1) This assumes you know ahead of time which columns you will want — notice that I intentionally left out ID = 1289 from this example

(1)这假定您提前知道您想要哪个列—注意，在这个示例中我故意省略了ID = 1289

(2) This basically uses a bunch of left outer joins to append 1 column at a time, thus creating the transposition. The left outer joins (rather than inner joins) allow for some columns to be null if they don't have corresponding values from the flat table, without affecting any subsequent columns.

(2)这基本上是一次使用一串左外连接附加1列，从而创建换位。左边的外部连接(而不是内部连接)允许某些列为null，如果它们没有来自flat表的相应值，而不会影响任何后续列。

#1