Q:

Dynamic pivot table - sql developer

I tried to create my own procedure for dynamic pivot but it isn't working. 
My procedure is created correctly but when I want to execute it - it isn't working. 
Firstly I had tried bluefeet's procedure and it was working. link to this question
 Dynamically pivoting a table Oracle
My procedure should count occurrence of values from column p_key.
Maybe somebody has any idea why my procedure is not working.
code for my table
create table aa_tabela (
C_ID number(3,0),
gcm number(1,0), 
p_key varchar2(30), 
p_count  number(6,0));

I insert few values
INSERT ALL 
 INTO aa_tabela (C_ID, gcm, p_key, p_count )
     VALUES (1, 7, 'Data Mining', 2)
 INTO aa_tabela (C_ID, gcm, p_key, p_count )
     VALUES (2, 5, 'Data Mining', 3)
 INTO aa_tabela (C_ID, gcm, p_key, p_count )
     VALUES (3, 3, 'Reporting', 1)
 INTO aa_tabela (C_ID, gcm, p_key, p_count )
     VALUES (4, 3, 'Olap', 5)
 INTO aa_tabela (C_ID, gcm, p_key, p_count )
     VALUES (5, 1, 'Reporting', 4)
SELECT * FROM dual;

My static PIVOT table is working:
  SELECT GCM,
   SUM(case when p_key = 'Data Mining' then p_count end) AS Data_Mining,
   SUM(case when p_key = 'Reporting' then p_count end) AS Reporting,
   SUM(case when p_key = 'Olap' then p_count end) AS OLAP 
from aa_tabela
group by  gcm
order by gcm desc;

Result:
   |GCM | DATA_MINING | REPORTING    |   OLAP   |

   | 7  |      2      |              |          |
   | 5  |      3      |              |          |
   | 3  |             |      1       |    5     |
   | 1  |             |      4       |          |  

My procedure for dynamic pivot creates correctly:
CREATE OR REPLACE procedure test_abc(p_cursor in out sys_refcursor)
AS
   sql_query long := 'select gcm ';

BEGIN
    for x in (select distinct p_key from aa_tabela order by 1)
    loop
        sql_query := sql_query ||
          ' , sum(case when p_key = '''||x.p_key||''' then p_count end) as '||x.p_key;

          dbms_output.put_line(sql_query);
    end loop;

    sql_query := sql_query || ' from aa_tabela group by gcm';

    open p_cursor for sql_query;
END;

/
PROCEDURE TEST_ABC compiled
I execute my procedure by following code:
variable x refcursor
exec test_abc(:x);
print x

Error Message:
Error starting at line : 215 in command -
exec test_abc(:x)
Error report -
ORA-00923: FROM keyword not found where expected
ORA-06512: at "MAGDA.TEST_ABC", line 16
ORA-06512: at line 1
00923. 00000 -  "FROM keyword not found where expected"

A:

You get error because one of values is "Data Mining". In your procedure it's translated to column alias like as Data Mining.
You need to change space to underscore or add quotaion marks in line 9:
      ' , sum(case when p_key = '''||x.p_key||''' then p_count end) as "'||x.p_key||'"';

