Oracle动态数据透视 [英] Oracle Dynamic Pivoting

问题描述

我有下表.我需要基于列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.

TABLEA

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       

推荐答案

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

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<>小提琴此处

You can find a test on db<>fiddle here

在Oracle 11g中，另一个很酷的技巧(由 Anton Scheffer 创建)可以在此博客<中找到/a>.但是您必须为其添加数据透视功能.
可以在此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<>小提琴此处

这篇关于Oracle动态数据透视的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！