更新时间:2023-11-26 21:53:34
将列转换为行称为 UNPIVOT
.您没有指定您使用的是哪个版本的 SQL Server,但有几种不同的方法可以获得结果.
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.
您可以将 SELECT
与 UNION ALL
一起使用:
You can use SELECT
with 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;
如果您使用的是 SQL Server 2005+,那么您可以使用 UNPIVOT 函数:
If you are using SQL Server 2005+, then you can use the UNPIVOT function:
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;
最后,除了 UNPIVOT 函数之外,您还可以将 CROSS APPLY
与 VALUES
(2008+) 或 UNION ALL
结合使用:>
Finally, instead of the UNPIVOT function you could also use CROSS APPLY
with either VALUES
(2008+) or UNION ALL
:
SELECT name, column, age
FROM yourtable
CROSS APPLY
(
VALUES
('age', cast(age as varchar(10)),
('gender', gender)
) c (column, value);
这些版本中的任何一个都会给您想要的结果.您会注意到我必须将 age
列转换为 varchar
.这是因为列的数据类型/长度(在逆透视中)必须相同,因为您将在最终结果中将它们转换为单个列.
Any of these versions will give you the result that you want. You'll note that I had to cast the age
column 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.