sql - UNPIVOT on an indeterminate number of columns

Question

How can I write a query that will unpivot a table that always has 1 row and many columns to a result set that has 2 columns: column_name and value. I understand the underlying structure of the table is where the real problem lies, but I cannot change that. This query must also not have any knowledge of the names and/or number of columns in the said table, as columns are being frequently added (again, I know, bad design, can't change it), and I don't want to have to update the query each time a new column is added. I've been able to accomplish something close using unpivot, but that query requires the column names to be hard coded.

Is this even possible?

Oracle 11gR2

Answer 1

It sounds like you want to unpivot the table (pivoting would involve going from many rows and 2 columns to 1 row with many columns). You would most likely need to use dynamic SQL to generate the query and then use the DBMS_SQL package (or potentially EXECUTE IMMEDIATE) to execute it. You should also be able to construct a pipelined table function that did the unpivoting. You'd need to use dynamic SQL within the pipelined table function as well but it would potentially be less code. I'd expect a pure dynamic SQL statement using UNPIVOT to be more efficient, though.

An inefficient approach, but one that is relatively easy to follow, would be something like

SQL> ed
Wrote file afiedt.buf

  1  create or replace type emp_unpivot_type
  2  as object (
  3    empno number,
  4    col   varchar2(4000)
  5* );
SQL> /

Type created.

SQL> create or replace type emp_unpivot_tbl
  2  as table of emp_unpivot_type;
  3  /

Type created.

SQL> ed
Wrote file afiedt.buf

  1  create or replace function unpivot_emp
  2  ( p_empno in number )
  3    return emp_unpivot_tbl
  4    pipelined
  5  is
  6    l_val varchar2(4000);
  7  begin
  8    for cols in (select column_name from user_tab_columns where table_name = 'EMP')
  9    loop
 10      execute immediate 'select ' || cols.column_name || ' from emp where empno = :empno'
 11         into l_val
 12       using p_empno;
 13      pipe row( emp_unpivot_type( p_empno, l_val ));
 14    end loop;
 15    return;
 16* end;
SQL> /

Function created.

You can then call that in a SQL statement (I would think that you'd want at least a third column with the column name)

SQL> ed
Wrote file afiedt.buf

  1  select *
  2*   from table( unpivot_emp( 7934 ))
SQL> /

     EMPNO COL
---------- ----------------------------------------
      7934 7934
      7934 MILLER
      7934 CLERK
      7934 7782
      7934 23-JAN-82
      7934 1301
      7934
      7934 10

8 rows selected.

A more efficient approach would be to adapt Tom Kyte's show_table pipelined table function.