UNPIVOT的列数不确定 [英] UNPIVOT on an indeterminate number of columns

问题描述

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

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.

这有可能吗?

Oracle 11gR2

Oracle 11gR2

推荐答案

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

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.

然后您可以在SQL语句中调用它(我认为您至少需要带有列名的第三列)

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.

一种更有效的方法是改编Tom Kyte的

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

这篇关于UNPIVOT的列数不确定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！