Have unpivot automatically grab column list (oracle 11g)

Question

This is a follow up question to http://stackoverflow.com/questions/2344403/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)
 )
);

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?

Answer 1

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.