SQL Server 2005,将列转换为行 [英] SQL Server 2005, turn columns into rows
问题描述
我正在尝试将桌子旋转90度:使列成行.不允许使用PIVOT,因为PIVOT需要聚合函数.
I am trying to turn a table 90 degrees: make columns rows. No PIVOT is allowed since PIVOT requires aggregate functions.
示例:
我有一个带有列的表:
ID int,
ISO字符(2),
文字varchar(255).
Example:
I have a table with the columns:
ID int,
ISO char(2),
Text varchar(255).
所以我有这个:
ID ISO Text
-- --- ----
1 DE Auto
2 EN Car
我想获得以下信息:
ID EN DE
-- --- ----
1 Car Auto
您是如何做到的?
推荐答案
这个答案确实是frantisek的,我只是在这里复制以更正错误(我无法直接编辑).
This answer is really frantisek's, I'm just copying here to correct the mistake (I can't edit directly).
基本上,您可以通过调整来使用该解决方案:
Basically, you use that solution, with a tweak:
SELECT
max(DE) as DE, max(EN) as EN
FROM
test
PIVOT (MAX([text]) FOR ISO in (DE,EN)) p
这会将内容分成一行.另外,它会删除ID,因为如果只需要一行就没有意义(合并成一行后,没有逻辑指示如何处理它).
This will get the content into a single row. Also, it drops the ID, since it doesn't make sense if you want a single row (there is no logic to indicate what to do with it when combining into a single row).
Also, the assumption is made that the values in the ISO column are unique, otherwise, this will lose data due to the MAX
aggregate.
这篇关于SQL Server 2005,将列转换为行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!