动态透视表Oracle [英] Dynamically pivoting a table Oracle

问题描述

我有一个看起来像这样的表:

I have a table that looks like this:

C_ID   P_ID   KEY    VALUE
null   null   KEY1   VALUE1
null   null   KEY2   VALUE2
null   null   KEY3   VALUE3
2       2     KEY4   VALUE4
2       3     KEY5   VALUE5

我想得到这个结果表/视图:

I want to get this result table/view:

C_ID   P_ID   KEY1    KEY2    KEY3      KEY4    KEY5
NULL   NULL  VALUE1  VALUE2  VALUE3     NULL    NULL
2       2     NULL    NULL    NULL     VALUE4   NULL
2       3     NULL    NULL    NULL      NULL   VALUE5

有人知道我该如何实现吗? 我已经尝试过:

Has anybody an Idea how I could achieve this? I have tried it with:

select * from (select c_id, p_id, r_key, r_value from s_projectroles) pivot (max(r_value) for r_key in (any));

我遇到一个错误:

ORA-00936: Ausdruck fehlt
00936. 00000 -  "missing expression"

推荐答案

这可以通过以下方式动态完成.首先，这是查询的静态版本，因此您可以看到最终的sql:

This can be done dynamically the following way. First, here is the static version of the query so you can see the final sql:

select c_id,
  p_id,
  max(case when r_key= 'KEY1' then r_value  end) KEY1,
  max(case when r_key= 'KEY2' then r_value  end) KEY2,
  max(case when r_key= 'KEY3' then r_value  end) KEY3,
  max(case when r_key= 'KEY4' then r_value  end) KEY4,
  max(case when r_key= 'KEY5' then r_value  end) KEY5
from s_projectroles
group by c_id, p_id

请参见带有演示的SQL小提琴

然后要动态执行此操作，可以创建以下过程:

Then to do this dynamically, you can create the following procedure:

CREATE OR REPLACE procedure dynamic_pivot(p_cursor in out sys_refcursor)
as
    sql_query varchar2(1000) := 'select c_id, P_id ';

    begin
        for x in (select distinct r_key from s_projectroles order by 1)
        loop
            sql_query := sql_query ||
              ' , max(case when r_key = '''||x.r_key||''' then r_value end) as '||x.r_key;

                dbms_output.put_line(sql_query);
        end loop;

        sql_query := sql_query || ' from s_projectroles group by c_id, p_id';

        open p_cursor for sql_query;
    end;
/

然后执行它:

variable x refcursor
exec dynamic_pivot(:x)
print x

结果相同:

|   C_ID |   P_ID |   KEY1 |   KEY2 |   KEY3 |   KEY4 |   KEY5 |
----------------------------------------------------------------
| (null) | (null) | VALUE1 | VALUE2 | VALUE3 | (null) | (null) |
|      2 |      2 | (null) | (null) | (null) | VALUE4 | (null) |
|      2 |      3 | (null) | (null) | (null) | (null) | VALUE5 |

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