SQL Server多列反转

Question

我将竭诚为您翻译。以下是要翻译的内容:

我试图围绕其许多列旋转数据表格，以得到3个列（旋转列、列名、值）

例如：

例如：

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

将成为：

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

我已经通过谷歌搜索了很多，但没有找到类似的情况 - 特别是因为这个中心点似乎是相反方向完成的，与我想要实现的相反。

- mathematician

Answer 1

把列转化成行被称作UNPIVOT。你没有说明使用的SQL Server版本，但有多种不同的方法可以获得结果。

你可以使用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;

如果您使用的是 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;

最后，你也可以使用CROSS APPLY函数，而不是UNPIVOT函数，使用VALUES（2008+）或UNION ALL：

SELECT name, column, age
FROM yourtable
CROSS APPLY
(
  VALUES
    ('age', cast(age as varchar(10)),
    ('gender', gender)
) c (column, value);

任何一个版本都可以给您想要的结果。请注意，我必须将 age 列转换为 varchar。这是因为在 unpivot 中，列的数据类型/长度必须相同，因为您将把它们转换为最终结果中的单个列。

- Taryn

---

在我的SQL 2008服务器上测试时，可能需要指出max_length也需要匹配，因此如果Gender是char(1)，它还需要被转换才能进行unpivot。不过我还没有尝试使用outer apply。 - Jaaz Cole

所以你的意思是，在我寻找如何使用数据透视表时，因为我只找到了相反的应用程序，我应该看看UNpivot...太棒了!! :) 谢谢! - mathematician

@JaazCole 只有 UNPIVOT 需要数据类型具有相同的长度 - 在 DBA.SE 上可以看到。 - Taryn

“CROSS APPLY” 的技巧真是太棒了。它似乎比使用 “UNPIVOT” 更加灵活，且个人认为更易读。 - jtate

CROSS APPLY is a great solution but I suspect you may need to quote [column] - CervEd

Answer 2

SELECT name, column, value
FROM (SELECT name, age, gender
FROM table) src
UNPIVOT (value FOR column IN (age, gender)) pvt

- L Vermeulen

Answer 3

WITH cte1 AS (SELECT ROW_NUMBER() OVER (ORDER BY adl_id, ma_id) AS rk, 
adl_id, 
ma_id, 
ISNULL(CONVERT(CHAR,fosa_id),'') fosa_id,
ISNULL(CONVERT(CHAR,kdah_id),'') kdah_id,
ISNULL(CONVERT(CHAR,lb_id),'') lb_id,
ISNULL(CONVERT(CHAR,sprach_id),'') sprach_id
FROM tm_lieferant WHERE adl_id=1004)
,cte2 AS (SELECT rk, fieldname, [value] FROM (
SELECT rk, fosa_id, kdah_id, lb_id, sprach_id FROM cte1 ) p
UNPIVOT([value] FOR fieldname IN (fosa_id, kdah_id, lb_id, sprach_id
)) AS unpvt)
,cte3 AS (SELECT cte1.adl_id, cte1.ma_id, cte2.fieldname, cte2.value FROM cte2 INNER JOIN cte1 ON cte2.rk=cte1.rk)
SELECT * FROM cte3

- BernhardP

---

这个问题已经有一个回答，获得了超过42个赞。为什么你更喜欢自己的方法而不是他们的？你能否编辑你的答案，包括解释你正在做什么以及为什么认为这是最好的方法？ - Jeremy Caney

Answer 4

你可以通过使用“WITH”子句和生成的辅助列轻松完成。在第一个CTE中，您生成临时列，在第二个CTE中，您将其展开，在第三个CTE中，您再次将其组合在一起。就这样！

- BernhardP