Oracle查询将多列转换为一列 [英] Oracle query to convert multiple column into one column

问题描述

我在一个表中有50列，它仅返回一行，并且我希望包含50列的那一行显示在50行和一列中.

I have 50 column in a table and it returns only one row and I want that one row with 50 column to be displayed in 50 rows and one column.

有人可以建议我对Oracle进行查询吗?

Can any one suggest me the Oracle query for it?

推荐答案

您可以将UNPIVOT用于这样的一行，以仅获取具有值的列

You can use UNPIVOT for one row like this to get only column with values

SELECT colvalue 
  FROM
(
  SELECT * 
    FROM Table1
  UNPIVOT INCLUDE NULLS
  (
    colvalue FOR cols IN (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, ... col50)
  )
);

示例输出:

| COLVALUE |
------------
|        1 |
|        2 |
|   (null) |
|..........|

如果您需要数据透视表中具有列名的列，则放弃外部选择

If you need column with column names from your pivoted table just ditch the outer select

SELECT * 
  FROM Table1
UNPIVOT INCLUDE NULLS
(
  colvalue FOR cols IN (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, ... col50)
);

示例输出:

|  COLS | COLVALUE |
--------------------
|  COL1 |        1 |
|  COL2 |        2 |
|  COL3 |   (null) |
| ..... |......... |

这里是 SQLFiddle 演示

Here is SQLFiddle demo

这篇关于Oracle查询将多列转换为一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！