sql – UNPIVOT对不确定数量的列

发布时间：2020-12-12 16:19:03 所属栏目：MsSql教程来源：网络整理

导读：如何编写一个查询,将一个总共有1行和多列的表取消为具有2列的结果集：column_name和value.我理解表的底层结构是真正的问题所在,但我不能改变它.这个查询也必须不知道所述表中的列名和/或列数,因为列经常被添加(再次,我知道,设计不好,不能改变它),我不想要每次

如何编写一个查询,将一个总共有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.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!