SQL Server 2005，把列变成行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/428041/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server 2005, turn columns into rows
提问by frantisek
I am trying to turn a table 90 degrees: make columns rows. No PIVOT is allowed since PIVOT requires aggregate functions.
我正在尝试将桌子旋转 90 度：使列成为行。由于 PIVOT 需要聚合函数，因此不允许使用 PIVOT。
Example:
I have a table with the columns:
ID int,
ISO char(2),
Text varchar(255).
示例：我有一个包含以下列的表：
ID int、
ISO char(2)、
Text varchar(255)。
So I have this:
所以我有这个：
ID ISO Text -- --- ---- 1 DE Auto 2 EN Car
I'd like to get the following:
我想得到以下内容：
ID EN DE -- --- ---- 1 Car Auto
How do you accomplish that?
你如何做到这一点？
回答by casperOne
This answer is really frantisek's, I'm just copying here to correct the mistake (I can't edit directly).
这个答案真的是 frantisek 的，我只是在这里复制以纠正错误（我无法直接编辑）。
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
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).
这会将内容放入一行。此外，它会删除 ID，因为如果您想要单行则没有意义（没有逻辑指示在组合成单行时如何处理它）。
Also, the assumption is made that the values in the ISO column are unique, otherwise, this will lose data due to the MAXaggregate.
此外，假设 ISO 列中的值是唯一的，否则，这将因MAX聚合而丢失数据。
回答by frantisek
I found the solution as the following:
我找到了以下解决方案：
SELECT
ID, DE, EN
FROM
TextTable
PIVOT(MAX([text]) FOR ISO IN (DE,EN)) p
It's possible to use PIVOT with MAX aggregating function over the text.
可以在文本上使用 PIVOT 和 MAX 聚合函数。
回答by codeConcussion
Query withouta PIVOT even though other answers prove you canuse a PIVOT :)
即使其他答案证明您可以使用 PIVOT也可以在没有PIVOT 的情况下进行查询:)
SELECT
MAX(DE.Text) AS DE,
MAX(EN.Text) AS EN
FROM TextTable AS TT
LEFT JOIN TextTable AS DE
ON DE.ID = TT.ID
AND DE.ISO = 'DE'
LEFT JOIN TextTable AS EN
ON EN.ID = TT.ID
AND EN.ISO = 'EN'
回答by Lehrian
If you try this solution and get a syntax error try setting the compatability mode of your database via
如果您尝试此解决方案并收到语法错误，请尝试通过以下方式设置数据库的兼容模式
ALTER DATABASE myDatabase SET COMPATIBILITY_LEVEL = 90;
This will set the compatability to SQLServer 2005 and the above queries will execute w/o a syntax error.
这将设置与 SQLServer 2005 的兼容性，并且上述查询将执行 w/oa 语法错误。
回答by Deep
select
t.num_claim_no,
rtrim (xmlagg (xmlelement (e, t.txt_remarks ||'@'|| t.dat_update_date || ' , ')).extract ('//text()'), ',') Remarks,
rtrim (xmlagg (xmlelement (e, t.num_update_no || ' , ')).extract ('//text()'), ',') SrlNo
from
gc_clm_gen_info t
where t.txt_remarks is not null
group by
t.num_claim_no
;
回答by splattne
Since you explicitly asked for a non-pivot solution: this should work, if you know which ISOs you will have in the rows. I called the table "Test".
由于您明确要求非枢轴解决方案：如果您知道行中将包含哪些 ISO，这应该可行。我称该表为“测试”。
declare @temp table ([ID] int, [de] varchar(255), [en] varchar(255)) -- add ISOs if necessary
INSERT @temp
SELECT distinct [ID], '', '' from Test -- ADD '' for other ISOs if necessary
DECLARE c CURSOR read_only
FOR SELECT [ID], [ISO], [Text] from test
DECLARE @ID int, @ISO char(2), @Text varchar(255)
OPEN c
FETCH NEXT FROM c INTO @ID, @ISO, @Text
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
UPDATE @temp
SET [DE] = case when @ISO = 'DE' then @Text else [de] end,
[EN] = case when @ISO = 'EN' then @Text else [en] end
-- add ISOs if necessary
WHERE [ID] = @ID
END
FETCH NEXT FROM c INTO @ID, @ISO, @Text
END
CLOSE c
DEALLOCATE c
SELECT * FROM @temp
