Oracle 查询:动态遍历列以创建行

Question

我有以下 Oracle 数据库，该数据库可以包含任意数量的列，这些列可能会随着时间的推移而增加，这些列可能会通过用户对 Web 应用程序的干预而增长。

ID | Name   | Football | WhoCreated  | When       | Baseball | Cheerleading | Swimming
1  | Billy  | (null)   |  sam, smith | (Timestamp)|   1      | (null)       |   1
2  | Susie  |  1       |  sam, smith | (Timestamp)| (null)   |   1          |   1
3  | Johnny |  1       |  Homer      | (Timestamp)|   1      | (null)       | (null)

我正在尝试生成一个看起来像

2, Susie, Football
3, Johnny, Football
1, Billy, Baseball
3, Johnny, Baseball
2, Susie, Cheerleading
1, Billy, Swimming
2, Susie, Swimming

我可以使用 UNION 来做到这一点，但我必须针对特定名称字段调整每个字段。我已经有大约 50 列（50 个联合），并且系统中的用户可以随时增长。为了使事情更加复杂，我在列表中间隐藏了几列用于审核目的的列。我真的需要某种动态的方式来循环浏览列，我已经搜索过了，但似乎没有一种能解决我遇到的问题。

Answer 1

这应该给你一个想法。我无法测试它，但相当容易理解。

create or replace package test_pkg AS
  TYPE REP_CURS IS REF CURSOR;
  TYPE output_REC IS RECORD(
    id_    number,
    name_  varchar2(50),
    field_ varchar2(50));
  TYPE output_TAB IS TABLE OF output_REC;
  FUNCTION Get_Data RETURN output_TAB
    PIPELINED;
END test_pkg;
CREATE OR REPLACE PACKAGE BODY test_pkg IS
  FUNCTION Get_Data RETURN output_TAB
    PIPELINED IS
    output_REC_ output_REC;
    rep_lines_  REP_CURS;
    stmt_       VARCHAR2(5000);
    table_rec_  yourtable%ROWTYPE;
  begin
    stmt_ := '  YOUR QUERY HERE ';
    OPEN rep_lines_ FOR stmt_;
    LOOP
      FETCH rep_lines_
        INTO table_rec_;
      EXIT WHEN rep_lines_%NOTFOUND;
      output_REC_.id_   := table_rec_.id;
      output_REC_.name_ := table_rec_.name;
      if table_rec_.football IS not null then
        output_REC_.field_ := table_rec_.football;
        PIPE ROW(output_REC_);
      end if;
      if table_rec_.Baseball IS not null then
        output_REC_.field_ := table_rec_.Baseball;
        PIPE ROW(output_REC_);
      end if;
      if table_rec_.Cheerleading IS not null then
        output_REC_.field_ := table_rec_.Cheerleading;
        PIPE ROW(output_REC_);
      end if;
      if table_rec_.Swimming IS not null then
        output_REC_.field_ := table_rec_.Swimming;
        PIPE ROW(output_REC_);
      end if;
    END LOOP;
    CLOSE rep_lines_;
    RETURN;
  exception
    when others then
      DBMS_OUTPUT.put_line('Error:' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ||
                           DBMS_UTILITY.FORMAT_ERROR_STACK ||
                           DBMS_UTILITY.FORMAT_CALL_STACK);
  END Get_Data;
END test_pkg;

Answer 2

你应该创建一个函数然后使用它，请考虑下面的代码：

CREATE OR REPLACE FUNCTION commalist (par_id NUMBER) RETURN VARCHAR2 IS 
  TYPE curs IS REF CURSOR;
  v_emp_cursor curs;
  fieldvalue   VARCHAR2(4000);
  var_out      VARCHAR2(4000);
  CURSOR col IS
  SELECT column_name
    FROM user_tab_columns
   WHERE table_name = 'TABLE';
BEGIN
  FOR reccol IN col LOOP
    OPEN v_emp_cursor FOR ('SELECT '||reccol.column_name||' val
                      FROM TABLE 
                     WHERE id = '||par_id);
    LOOP
      FETCH v_emp_cursor INTO fieldvalue;
        IF fieldvalue IS NOT NULL THEN
          var_out := var_out||fieldvalue||', ';
        END IF;
      EXIT WHEN v_emp_cursor%NOTFOUND;
    END LOOP;
    CLOSE v_emp_cursor;
  END LOOP;
  RETURN SubStr(var_out, 0, Length(var_out) - 2);
END;

Answer 3

试试这个

with src as (select 1 ID, 'Billy' name, 0 Football, 'sam smith' WhoCreated, sysTimestamp when,
                   1 Baseball, cast(null as number) Cheerleading, 1 Swimming
              from dual
            union all
            select 2, 'Susie', 1, 'sam smith', sysTimestamp, null, 1, 1
              from dual
            union all
            select 3, 'Johnny', 1, 'Homer', sysTimestamp, 1, null, null 
              from dual),
unpivottbl as (select *
                from src
                UNPIVOT 
                (
                   VAL
                   FOR descript
                   IN (Football, Baseball, Cheerleading, Swimming)
                ))
select ID, name, descript 
  from unpivottbl 
 where VAL = 1

虽然它不是动态解决方案，但您需要添加要对其数据进行反透处理的列。这是取消透视的参考。