取消透视自动获取列列表(oracle 11g) [英] Have unpivot automatically grab column list (oracle 11g)

问题描述

这是对将Oracle的一行转换成多行的后续问题/a>

This is a follow up question to Transpose one row into many rows Oracle

我希望能够取消任意查询结果.

I want to be able to unpivot an arbitrary query result.

要手动取消数据透视表的操作，我会这样做:

To unpivot a table manually, I would do:

select value_type, value from (
 (
  -- query to be unpivoted
  -- EG: select col1, col2, col3, col4, col5 from table
 )
 unpivot
 (
  -- Line I would like to change
  value for value_type in (col1, col2, col3, col4, col5)
 )
);

这适用于所有返回5列(称为col1, col2等)的查询.我是否放了一些东西而不是value for value_type in (col1, col2, col3, col4, col5)，它将从查询中选择的查询/视图/表中获取所有列名.第一部分?

This works for all queries that return 5 columns, called col1, col2, etc. Is there something I put in instead of value for value_type in (col1, col2, col3, col4, col5) that will grab all the column names from the query/view/table that is selected in the first part?

推荐答案

您可以创建存储过程以在PL/SQL中执行此操作，方法是将SQL语句动态创建为字符串，然后使用execute immediate执行它并返回光标.

You could create a stored procedure to do this in PL/SQL by dynamically creating your SQL statement as a string an then using execute immediate to execute it and return a cursor.

这篇关于取消透视自动获取列列表(oracle 11g)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！