行与列相反

发布于 2024-10-22 01:12:38 字数 481 浏览 2 评论 0原文

我有一个更大的 SQL，它产生与这个简化示例类似的输出：

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 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

current output is this row:

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

desired output are this columns:

| kind | value |
|   0  |   5   |
|   1  |   2   |
|   2  |   3   |
|   3  |   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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

夢归不見 2024-10-29 01:12:38

请尝试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) );

假设您实际上有一个可以查询的表。

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));

Inversely:

 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.

回复收藏 0 原文

凌乱心跳 2024-10-29 01:12:38

<代码>

select i - 1 kind,

    case when i = 1 then L0
         when i = 2 then L1
         when i = 3 then L2
         when i = 4 then L3
     end  as value
  from your_table,
       (select level as i from dual connect by level <= 4)

This does a Carteasan Product of your table and the "inline view." The inline view will output four rows, 1 through 4.

select i - 1 kind,

    case when i = 1 then L0
         when i = 2 then L1
         when i = 3 then L2
         when i = 4 then L3
     end  as value
  from your_table,
       (select level as i from dual connect by level <= 4)

This does a Carteasan Product of your table and the "inline view." The inline view will output four rows, 1 through 4.

回复收藏 0 原文

~没有更多了~