UNPIVOT on an indefinite number of columns

Question

How can I write a query that will close a table that always has 1 row and many columns for a result set that has 2 columns: column_name and value. I understand that the basic structure of the table is where the problem is, but I cannot change it. This query also does not need to know the names and / or the number of columns in the specified table, because columns are often added (again, I know a bad design, I can’t change it), and I don’t want to update the query every time a new column is added . I was able to do something closely using univot, but this query requires the column names to be hardcoded.

Is it possible?

Oracle 11gR2

Answer 1

It looks like you want to expand the table (the rotation will include a transition from many rows and from 2 to 1 rows with many columns). Most likely, you will need to use dynamic SQL to generate the query, and then use the package DBMS_SQL(or potentially EXECUTE IMMEDIATE) to execute it. You should also be able to construct a pipeline table function that has not been opened. You will also need to use dynamic SQL inside the table pipeline function, but this may be less than code. I would expect a pure dynamic SQL statement using UNPIVOTto be more efficient.

An ineffective approach, but one that is relatively easy to follow will 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.

SQL ( , )

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.

Tom Kate show_table .