问题描述
我已经到处搜索了这个问题的答案，如果已经回答了，我们深表歉意！我从 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 - 将行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！