oracle - 如何使用动态列取消透视 Oracle

Question

我需要取消透视一个我无法控制列的表，因此我需要动态获取列名称:这就是我所拥有的

CREATE TABLE test
(
 PK VARCHAR2(255 CHAR),
COL1                VARCHAR2(255 CHAR),
COL2              VARCHAR2(255 CHAR),
COL3            VARCHAR2(255 CHAR),
COL4              VARCHAR2(255 CHAR),
COL5             VARCHAR2(255 CHAR),
COL6            NUMBER,

)

    declare
      sql_stmt     clob;
      pivot_clause clob;
    begin
      select listagg('''' || column_name || ''' as "' || column_name || '"', ',') within group (order by column_name) 
      into   pivot_clause
    FROM USER_TAB_COLUMNS
    WHERE table_name = 'test');



      sql_stmt := 'SELECT PK,

    VarName,
    Valuer,
    Max(timestamp) over (Partition by PK) as timestamp,

    FROM   test
    UNPIVOT(Valuer FOR VarName IN (' || pivot_clause || '))';

  execute immediate sql_stmt;
end;

返回结果:

Fehlerbericht -
ORA-00904: : invalid identifier
ORA-06512: at line 23
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

预期输出如下:

PK|VARNAME|Valuer
1 |Col1 | value1
1 |Col2 | value2
1 |Col3 | value3
1 |Col4 | value4
1 |Col5 | value5
1 |Col6 | 12345
2 |Col1 | value1
2 |Col2 | value2
2 |Col3 | value3
2 |Col4 | value4
2 |Col5 | value5
2 |Col6 | 12345

如果我只是将子选择直接扔到 IN() 中，这与我得到的错误相同

谢谢

Answer 1

您可以使用dbms_output.put_line(sql_stmt)来查看实际生成的动态SQL，在本例中为:

SELECT PK,

    VarName,
    Valuer,
    Max(timestamp) over (Partition by PK) as timestamp,

    FROM   test
    UNPIVOT(Valuer FOR VarName IN ('COL1' as "COL1",'COL2' as "COL2",'COL3' as "COL3",'COL4' as "COL4",'COL5' as "COL5",'COL6' as "COL6",'PK' as "PK",'TIMESTAMP' as "TIMESTAMP"))

这有很多问题。一般来说，对于这类事情，明智的做法是从您知道有效的静态语句开始，然后找出如何使其动态化，但您似乎没有在这里这样做。

由于动态语句中时间戳后面有尾随逗号，此版本出现ORA-00936:缺少表达式。我想这是修改发布代码时出现的另一个错误。如果没有它，它会得到您问题中的 ORA-00904: : invalididentifier 。您收到的错误的直接原因是括号中的部分，例如:

'COL1' as "COL1"

引用错误；那应该是:

COL1 as 'COL1'

修复此问题后会出现ORA-00904: "PK": invalididentifier，因为您的列查找并未排除您不想旋转的列，所以您确实想要:

  select listagg(column_name || ' as ''' || column_name || '''', ',')
    within group (order by column_name) 
  into   pivot_clause
  from user_tab_columns
  where table_name = 'TEST'
  and column_name not in ('PK', 'TIMESTAMP');

您的下一个问题是您要取消透视的列是不同的数据类型，因此您会得到ORA-01790:表达式必须与相应表达式具有相同的数据类型。这有点棘手 - 本质上你必须使用合适的格式将所有内容转换为字符串，特别是在涉及日期的情况下。您需要在子查询中进行该转换，然后对结果进行逆透视。一个仅显式处理数字列的示例，以与 unpivot 子句相同的方式生成子查询列:

declare
  sql_stmt     clob;
  subquery     clob;
  pivot_clause clob;
begin
  select listagg(column_name || ' as ''' || column_name || '''', ',')
      within group (order by column_name),
    listagg(
    case when data_type = 'NUMBER' then 'to_char(' || column_name || ')'
      else column_name
      end || ' as ' || column_name, ',')
      within group (order by column_name)
  into   pivot_clause, subquery
  from user_tab_columns
  where table_name = 'TEST'
  and column_name not in ('PK', 'TIMESTAMP');

  sql_stmt := 'select pk,
    varname,
    valuer,
    max(timestamp) over (partition by pk) as timestamp
    from (select pk, timestamp, ' || subquery || ' from test)
    unpivot(valuer for varname in (' || pivot_clause || '))';

  dbms_output.put_line(sql_stmt);
  execute immediate sql_stmt;
end;
/

您可以始终将第二个 listagg() 中的每一列转换为 varchar2(255)，但这样您就无法直接控制格式设置，并且依赖于NLS 设置。

当运行上面的 block 时，现在会生成:

select pk,
    varname,
    valuer,
    max(timestamp) over (partition by pk) as timestamp
    from (select pk, timestamp, COL1 as COL1,COL2 as COL2,COL3 as COL3,COL4 as COL4,COL5 as COL5,to_char(COL6) as COL6 from test)
    unpivot(valuer for varname in (COL1 as 'COL1',COL2 as 'COL2',COL3 as 'COL3',COL4 as 'COL4',COL5 as 'COL5',COL6 as 'COL6'))

当手动运行时，会得到如下输出:

PK  VARNAME  VALUER   TIMESTAMP                     
1   COL1     value1   11-AUG-17 15.49.42.239283000  
1   COL2     value2   11-AUG-17 15.49.42.239283000  
1   COL3     value3   11-AUG-17 15.49.42.239283000  
1   COL4     value4   11-AUG-17 15.49.42.239283000  
1   COL5     value5   11-AUG-17 15.49.42.239283000  
1   COL6     12345    11-AUG-17 15.49.42.239283000  
2   COL1     value6   11-AUG-17 15.49.42.340387000  
2   COL2     value7   11-AUG-17 15.49.42.340387000  
2   COL3     value8   11-AUG-17 15.49.42.340387000  
2   COL4     value9   11-AUG-17 15.49.42.340387000  
2   COL5     value10  11-AUG-17 15.49.42.340387000  
2   COL6     23456    11-AUG-17 15.49.42.340387000  

(我在未透视列中设置了具有不同值的虚拟数据，并且仅使用 systimestamp 来获取时间戳列值)。

当你动态运行它时，它实际上并没有做任何事情 - 它被解析但没有完全执行，因为你没有立即执行任何东西。

My plan was to use this in a view

您可以让匿名 block 动态生成 View ，作为一次性事件:

  sql_stmt := 'create or replace view your_view_name as
    select pk,
      varname,
      valuer,
      max(timestamp) over (partition by pk) as timestamp
      from (select pk, timestamp, ' || subquery || ' from test)
      unpivot(valuer for varname in (' || pivot_clause || '))';

  execute immediate sql_stmt;

然后您只需选择from your_view_name即可。

每当将新列添加到表中时(希望这种情况很少见并且处于更改控制之下 - 但您实际上不需要动态执行此操作)，您只需重新运行该 block 即可重新创建 View 。