行与列的倒数 [英] inverse row to column

问题描述

我有一个较大的SQL，它产生的输出与此简化示例类似:

I have a larger SQL that produces a similar output as this simplified example:

SELECT
  5 AS L0,
  2 AS L1,
  3 AS L2,
  4 AS L3
FROM DUAL

当前输出是此行:

| L0 | L1 | L2 | L3 |
|  5 |  2 |  3 |  4 |

所需的输出是以下列:

| kind | value |
|   0  |   5   |
|   1  |   2   |
|   2  |   3   |
|   3  |   4   |

我知道我可以通过选择4次联合获得此结果. 我正在寻找建议，如果工会是我在这里可以做的最好的事情，以及是否可以通过其他方式实现此输出.

I know I could get this by union the select 4 times. I'm looking for advice if union is the best I can do here and if this output can be achieved by other means.

我还发现了许多示例，可以将列反转为行，但是在这里，我正在寻找从行到列的反转.

I also found many examples to inverse a column to a row but here I'm looking for inversion from a row to column.

推荐答案

请尝试UnPIVOT:

Please try UnPIVOT:

SELECT substr(kind,2,1) AS kind,"value" FROM 
(    
    SELECT
        5 AS l0,
        2 AS l1,
        3 AS l2,
        4 AS l3
    FROM dual
)temp unpivot include NULLS ("value" FOR kind IN (l0, l1 , l2, l3));

相反:

 SELECT *   
 FROM 
 (
      SELECT Kind,
             max(Value) Value
      FROM table 
      GROUP BY Kind
 ) PIVOT ( max(Value) FOR Kind IN (L0, L1, L2, L3) );

假设您实际上有一个要查询的表.

Assuming that you actually have a table to query from.

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