UNPIVOT的列数不确定

一尘不染

UNPIVOT的列数不确定

sql

如何编写一个查询，该查询会将始终具有1行和许多列的表取消透视表到具有2列的结果集中：column_name和value。我知道表格的底层结构是真正的问题所在，但是我无法改变这一点。该查询还必须不了解所述表中的列的名称和/或数量，因为经常添加列（同样，我知道，不良的设计，无法更改它），我也不想每次添加新列时都必须更新查询。我已经能够使用unpivot完成一些操作，但是该查询要求对列名进行硬编码。

这有可能吗？

oracle11gR2

阅读 134

2021-03-10

共1个答案

一尘不染

听起来好像要取消透视表（透视将涉及从多行和2列到具有多列的1行）。您很可能需要使用动态SQL来生成查询，然后使用DBMS_SQL包（或可能使用EXECUTE IMMEDIATE）执行查询。您还应该能够构造完成透视的流水线表功能。您还需要在流水线表函数中使用动态SQL，但这可能会减少代码量。我希望使用纯动态SQL语句UNPIVOT会更高效。

一种效率低下的方法，但相对容易遵循的方法可能是：

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
Kyte的show_table流水线表函数。

2021-03-10