SQL Server - 将行转换为列 [英] SQL Server - Transpose rows into columns

问题描述

我已经到处搜索了这个问题的答案，如果已经回答了，我们深表歉意！我从 SQL 2005 中的查询得到以下结果:

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:

ID

1234

1235

1236

1267

1278

我想要的是

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

我无法完全理解枢轴运算符，但这看起来会涉及到它.我现在可以只使用 5 行，但好处是它是动态的，即可以扩展到 x 行.

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.

我最终想要的是将每个结果列的值分配给变量，例如

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]

推荐答案

您还需要在查询中为每个 id 聚合一个值字段.然后你可以做这样的事情

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

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

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