SQL：真正的转置

Question

我了解透视和反透视。但这不是我想要的。透视和反透视会聚合数据，但这不是我想要的。

把表格看作矩阵（线性代数）。如果我有一个 m x n 的矩阵，我想把它转换成一个 n x m 的矩阵。我需要一个真正的转置。

在 SQL 中如何实现这个操作？

例如，如果我有：

1  2  3
1  2  4
6  7  8
3  2  1
3  9  1

那么结果应该是：

1  1  6  3  3
2  2  7  2  9
3  4  8  1  1

注意行数变成了列数，反之亦然。还要注意我没有对任何数据进行分组或聚合。源中的每个值都出现在结果中，并且它们的x-y坐标已经交换。

- mtmurdock

---

你能发布一些样本数据和期望的结果吗？ - Taryn

我正在使用MS SQL Server 2008。更多细节已发布。 - mtmurdock

@mtmurdock，你认为“PIVOT”不起作用的原因是什么？ - Taryn

我知道透视表不会起作用，因为这不是透视表的工作方式。如果你能向我展示一个可以做到这一点的透视表，那就请尽管。但我有90%的把握它是做不到的。 - mtmurdock

我不确定为什么这个问题被关闭了，因为它对我来说似乎非常简单明了。在SQL中如何转置数据？甚至有一个答案得到了多个赞，并解决了我的问题。 - Quesi

可能是在Sql中转置列和行的简单方法？的重复问题。 - RichardTheKiwi

Answer 1

我不确定你为什么认为你不能用 UNPIVOT 和 PIVOT 完成这个任务：

select [1], [2], [3], [4], [5]
from 
(
  select *
  from
  (
    select col1, col2, col3,
      row_number() over(order by col1) rn
    from yourtable
  ) x
  unpivot
  (
    val for col in (col1, col2, col3)
  ) u
) x1
pivot
(
  max(val)
  for rn in ([1], [2], [3], [4], [5])
) p

请查看带有演示的SQL Fiddle。如果需要，这也可以动态执行。

如果需要保留列的顺序，则可以使用类似以下代码的方式，在表中的某一列上应用row_number()而不使用order by（这里有一篇关于使用非确定性行号的文章）：

select [1], [2], [3], [4], [5]
from 
(
  select *
  from
  (
    select col1, col2, col3,
      row_number() 
        over(order by (select 1)) rn
    from yourtable
  ) x
  unpivot
  (
    val for col in (col1, col2, col3)
  ) u
) x1
pivot
(
  max(val)
  for rn in ([1], [2], [3], [4], [5])
) p;

请查看带有演示的SQL Fiddle

- Taryn

---

哇，这真的非常接近了。干得好。唯一的问题是，在使用order by col1时，您重新排列了列。有什么方法可以保留顺序吗？ - mtmurdock

我看了你有关保留列顺序的编辑，但我不明白这是在做什么。它似乎对我没有起作用。 - mtmurdock

行号如何影响列顺序？ - mtmurdock

我发现第二个确实保留了列顺序，但没有保留行顺序。我的行（之前是列）现在按字母顺序排序... 有什么办法可以解决这个问题吗？ - Josh

@Josh，你能否发布一个 SQL Fiddle 并提供你的问题样例？ - Taryn