SQL Server 反透视多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24828346/
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 unpivot multiple columns
提问by mathematician
I'm trying to pivot a table around it's many columns to get to 3 columns (pivot, column name, value)
我正在尝试将表格围绕它的许多列旋转到 3 列（枢轴、列名、值）
so for example:
所以例如：
name | age | gender
------+-------+---------
John | 20 | M
Jill | 21 | F
would become:
会成为：
name | column | value
-----+--------+-------
John | age | 20
John | gender | M
Jill | age | 21
Jill | gender | F
I've googled quite a bit but haven't found a similar situation - especially since the pivot seems to be done in the opposite direction as what I'm trying to accomplish.
我在谷歌上搜索了很多，但没有发现类似的情况 - 特别是因为支点似乎与我想要完成的方向相反。
回答by Taryn
The conversion of columns into rows is called an UNPIVOT. You didn't specify what version of SQL Server you are using but there are several different ways to get the result.
将列转换为行称为UNPIVOT. 您没有指定所使用的 SQL Server 版本，但有几种不同的方法可以获得结果。
You can use SELECTwith UNION ALL:
你可以用SELECT与UNION ALL：
SELECT name, 'age' as column, cast(age as varchar(10)) as value
FROM yourtable
UNION ALL
SELECT name, 'gender' as column, gender as value
FROM yourtable;
If you are using SQL Server 2005+, then you can use the UNPIVOT function:
如果您使用的是 SQL Server 2005+，那么您可以使用 UNPIVOT 函数：
SELECT name, column, age
FROM
(
SELECT
name,
age = cast(age as varchar(10)),
gender
FROM yourtable
) d
UNPIVOT
(
value
for column in (age, gender)
) unpiv;
Finally, instead of the UNPIVOT function you could also use CROSS APPLYwith either VALUES(2008+) or UNION ALL:
最后，而不是UNPIVOT功能你也可以使用CROSS APPLY具有两种VALUES（2008+）或UNION ALL：
SELECT name, column, age
FROM yourtable
CROSS APPLY
(
VALUES
('age', cast(age as varchar(10)),
('gender', gender)
) c (column, value);
Any of these versions will give you the result that you want. You'll note that I had to cast the agecolumn to a varchar. This is because the datatype/length (in unpivot) of the columns must be the same since you will be transforming them into a single column in the final result.
这些版本中的任何一个都会给你你想要的结果。您会注意到我必须将age列强制转换为varchar. 这是因为列的数据类型/长度（在逆透视中）必须相同，因为您将在最终结果中将它们转换为单个列。
回答by L Vermeulen
SELECT name, column, value
FROM (SELECT name, age, gender
FROM table) src
UNPIVOT (value FOR column IN (age, gender)) pvt
