SQL:真正的转置 [英] SQL: Real Transpose

问题描述

我了解枢轴和枢轴.那不是我想要的.枢轴和取消枢轴聚合数据，但这不是我想要的.

I know about pivot and unpivot. That is not what I want. Pivot and unpivot aggregate data, but that is not what I want.

将表视为矩阵(线性代数).如果我从m x n矩阵开始，我想将该矩阵(表)转换为n x m矩阵.我想要一个真正的翻译.

Think of a table as a matrix (linear algebra). If I start with an m x n matrix, I want to convert that matrix (table) into an n x m matrix. I want a true TRANSPOSE.

如何在SQL中执行此操作?

How can I do this in 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坐标已互换.

Notice that the number of rows becomes the number of columns, and vice versa. Also notice that I have not grouped or aggregated any of the data. Every single value present in the source is present in the result, and their x-y coordinates have been swapped.

推荐答案

我不确定为什么您认为无法使用UNPIVOT和PIVOT来完成此任务:

I am unsure why you think you cannot accomplish this with an UNPIVOT and a 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提琴.如果需要，这也可以动态执行.

See SQL Fiddle with Demo. This could also be performed dynamically if needed.

编辑，如果需要保留列顺序，则可以使用类似的方法，该方法将应用row_number()而不在表中的任一列上使用order by(此处为有关使用不确定性行号的文章):

Edit, if the column order needs to be kept, then you can use something like this, which applies the row_number() without using a order by on one of the columns in your table (here is an article about using non-deterministic row numbers):

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小提琴

这篇关于SQL:真正的转置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！