pivot - Dynamically pivoting a table Oracle -

i have table looks 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 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 idea how achieve this? have tried with:

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

i got error:

ora-00936: ausdruck fehlt 00936. 00000 -  "missing expression" 

this can done dynamically following way. first, here static version of query can see final sql:

select c_id,   p_id,   max(case when r_key= 'key1' r_value  end) key1,   max(case when r_key= 'key2' r_value  end) key2,   max(case when r_key= 'key3' r_value  end) key3,   max(case when r_key= 'key4' r_value  end) key4,   max(case when r_key= 'key5' r_value  end) key5 s_projectroles group c_id, p_id 

see sql fiddle demo

then dynamically, can create following procedure:

create or replace procedure dynamic_pivot(p_cursor in out sys_refcursor)     sql_query varchar2(1000) := 'select c_id, p_id ';      begin         x in (select distinct r_key s_projectroles order 1)         loop             sql_query := sql_query ||               ' , max(case when r_key = '''||x.r_key||''' r_value end) '||x.r_key;                  dbms_output.put_line(sql_query);         end loop;          sql_query := sql_query || ' s_projectroles group c_id, p_id';          open p_cursor sql_query;     end; / 

then execute it:

variable x refcursor exec dynamic_pivot(:x) print x 

the result same:

|   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 |