Oracle 动态透视

2021-12-06 00:00:00 pivot sql oracle

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

I have the below table. I need to create columns based off the column CCL. The values in column CCL are unknown. I'm not sure where to begin here. Any help would be appreciated.

桌子

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

推荐答案

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

Using dynamic sql for a result where the columns are unknown at the time of executing is a bit of a hassle in Oracle compared to certain other RDMBS.

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

Because the record type for the output is yet unknown, it can't be defined beforehand.

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

In Oracle 11g, one way is to use a nameless procedure that generates a temporary table with the pivoted result.

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

Then select the results from that temporary table.

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 此处找到测试一个>

在 Oracle 11g 中，另一个很酷的技巧(由 Anton Scheffer 创建)可以在此博客.但是您必须为其添加枢轴函数.
源代码可以在在这个zip

In Oracle 11g, another cool trick (created by Anton Scheffer) to be used can be found in this blog. But you'll have to add the pivot function for it.
The source code can be found in this zip

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

After that the SQL can be as simple as this: 

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

您会在db<>fiddle 此处

相关文章