title: Dynamic Cross Tab Query in Oracle
body: <p>i need to create a dynamic cross tab query where columns will not always be fixed in number so cant hard code using case when. i ve googled, it did find a blog about doing the same in SQL Server but i was wondering if there is any such article blog on doing the same in Oracle. Have not worked in SQL Server. Fol is the info about my problem.</p>
  
  <p>the hard coded cross tab query i wrote </p>
  
  <pre><code>SELECT
  
  LU_CITY.CITY_NAME as "City",
  count(CASE WHEN emp.emp_category='Admin'  THEN emp.emp_id END) As "Admins",
  count(CASE WHEN emp.emp_category='Executive'  THEN emp.emp_id END) As "Executive",
  count(CASE WHEN emp.emp_category='Staff'  THEN emp.emp_id END) As "Staff",
  count(emp.emp_id) As "Total"
  
  FROM emp, LU_CITY
  
  where
  
     LU_CITY.CITY_ID = EMP.CITY_ID(+)
  group by
  LU_CITY.CITY_NAME, LU_CITY.CITY_ID
  order by
  LU_CITY.CITY_ID
  </code></pre>
  
  <p>tables</p>
  
  <pre><code>        emp (emp_id, emp_name, city_id, emp_category)
          lu_city(city_id,city_name)
  </code></pre>
  
  <p>query result</p>
  
  <pre><code>          ------------------------------------------
            City | Admins | Executive | Staff . . . .
            ------------------------------------------ 
            A    |    1   |   2       | 3
            B    |    0   |   0       | 4
            .    |    .   |   .       | . 
            .
            .
  </code></pre>
  
  <p>The emp_category can be added by the user as per their need. the query should be such that it should generate all such categories dynamically.</p>
  
  <p>Any guidance in this regard would be highly appreciated.</p>
  
  <p>Thanks in Advance</p>