sql - Oracle 动态透视

Question

我有下表。我需要基于列 CCL 创建列。 CCL 列中的值未知。我不知道从哪里开始。任何帮助，将不胜感激。

表

ID    CCL    Flag
1     john     x
1     adam     x
1     terry
1     rob      x
2     john     x

询问:

SELECT *
FROM TABLEA

输出:

ID  John  Adam  Terry  Rob
 1    x     x           x
 2    x       

Answer 1

与某些其他 RDMBS 相比，对于在执行时列未知的结果使用动态 sql 在 Oracle 中有点麻烦。

由于输出的记录类型尚不清楚，因此无法预先定义。

在 Oracle 11g 中，一种方法是使用一个无名过程来生成一个带有透视结果的临时表。

然后从该临时表中选择结果。

declare
  v_sqlqry clob;
  v_cols clob;
begin
  -- Generating a string with a list of the unique names
  select listagg(''''||CCL||''' as "'||CCL||'"', ', ') within group (order by CCL)
  into v_cols
  from 
  (
    select distinct CCL
    from tableA
  );

  -- drop the temporary table if it exists
  EXECUTE IMMEDIATE 'DROP TABLE tmpPivotTableA';
  EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF;

  -- A dynamic SQL to create a temporary table 
  -- based on the results of the pivot
  v_sqlqry := '
    CREATE GLOBAL TEMPORARY TABLE tmpPivotTableA
    ON COMMIT PRESERVE ROWS AS
    SELECT * 
    FROM (SELECT ID, CCL, Flag FROM TableA) src 
    PIVOT (MAX(Flag) FOR (CCL) IN ('||v_cols||')) pvt';

  -- dbms_output.Put_line(v_sqlqry); -- just to check how the sql looks like
  execute immediate v_sqlqry;

end;
/

select * from tmpPivotTableA;

返回:

ID  adam john rob terry
--  ---- ---- --- -----
1   x    x    x
2        x      

您可以在 db<>fiddle here 上找到测试

在 Oracle 11g 中，另一个很酷的技巧(由 Anton Scheffer 创建)可以在此 blog 中找到。 .但是您必须为它添加枢轴函数。
源码可以在 in this zip找到

之后，SQL 可以像这样简单:

select * from 
table(pivot('SELECT ID, CCL, Flag FROM TableA'));

你会在 db<>fiddle here 上找到一个测试