如何编写一个查询,将一个总共有1行和多列的表取消为具有2列的结果集：column_name和value.我理解表的底层结构是真正的问题所在,但我不能改变它.这个查询也必须不知道所述表中的列名和/或列数,因为列经常被添加(再次,我知道,设计不好,不能改变它),我不想要每次添加新列时都必须更新查询.我已经能够使用unpivot完成某些事情,但该查询要求列名称是硬编码的.
这有可能吗？
Oracle 11gR2
解决方法
听起来你想要将表格取消(旋转将涉及从许多行和2列到具有许多列的1行).您很可能需要使用动态sql生成查询,然后使用DBMS_sql包(或可能是EXECUTE IMMEDIATE)来执行它.您还应该能够构建一个执行unpivoting的流水线表函数.您还需要在流水线表函数中使用动态sql,但它可能会减少代码.不过,我期望使用UNPIVOT的纯动态sql语句更有效.
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 pipelined table function.
