SQL Server 反透视多列 [英] SQL Server unpivot multiple columns

问题描述

我正在尝试将表格围绕它的许多列旋转到 3 列(数据透视、列名称、值)

I'm trying to pivot a table around it's many columns to get to 3 columns (pivot, column name, value)

例如:

name  |  age  |  gender
------+-------+---------
John  |   20  |    M
Jill  |   21  |    F

会变成:

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.

推荐答案

将列转换为行称为 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.

这篇关于SQL Server 反透视多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！