title: Rows into Columns Oracle pl sql
body: <p>I am using oracle 10g pl sql, I want to transpose rows into columns but the columns should be dynamic and not hard-coded.</p>
  
  <pre><code>select *
  from
   "EnumerationValue"
     pivot
      (count("pkEnumerationValueId")
        for "Text" in (select "Text" from "EnumerationValue" where "fkEnumerationId"=6));
  </code></pre>
  
  <p>I found this query by searching but its giving me error.</p>
  
  <pre><code>ORA-00933: SQL command not properly ended
  00933. 00000 -  "SQL command not properly ended"
  </code></pre>
  
  <p>Then I tried like this</p>
  
  <pre><code>VARIABLE g_ref REFCURSOR
  DECLARE
   v_sql VARCHAR2(32767);
    BEGIN
     v_sql := 'select "Text"';
     FOR rec IN
       (select "Text" from "EnumerationValue" inner join "Enumeration" on "Enumeration"."pkEnumerationId"="EnumerationValue"."fkEnumerationId"
     where "EnumTable"='Vehicle Model')
     LOOP
       v_sql := v_sql
             || ',sum(case when "Text" is not null then 1 else 0)' || rec."Text";
      END LOOP;
    v_sql := v_sql
           || ',"pkEnumerationValueId" from "EnumerationValue" group by "Text","pkEnumerationValueId"';
   OPEN :g_ref FOR v_sql;
   END;
  </code></pre>
  
  <p>it is giving me error</p>
  
  <pre><code>ORA-00905: missing keyword
  ORA-06512: at line 14
  00905. 00000 -  "missing keyword"
  </code></pre>
  
  <p>My table column is "Text" containing values like "Suzuki","Ferrari","Honda","Ford" and it can be more (user can insert new values).
  So I want a result like this.</p>
  
  <pre><code>Model        Suzuki        Ferrari     Honda      Ford   ........
  
  2000           1              0          0          0
  2001           5              2          5          0
  2002           9              12         3          2
  </code></pre>