plsql - Pivoting row dynamically in oracle database -

below scenario:

(* denotes primary key) **companyunit**(*unit_id, unit_loc,year);  **employees**(unit_id,dept_id,no_of_emp); (unit_id , dept_id foreign keys)  **ref_department**(*dept_id,dept_name,dept_desc); 

sample data:

---

unit_id       unit_loc        year ------------------------------------ 1             delhi            2003 2             mumbai           2004 ------------------------------------ dept_id       dept_name        dept_desc ---------------------------------------- 101           abc              abc-ai 102           abc              abc-bi 103           abc              abc-cs 104           xyz              xyz-testing 105           xyz              xyz-development ---------------------------------------------- unit_id       dept_id          no_of_emp ---------------------------------------------- 1             101              5000 2             102              3000 1             103              4000 1             104              2000 2             105              1000 2             101              3000 ----------------------------------------------  required output: dynamic view or select: --------------------------------------------------------------------------- unit_id  unit_loc   abc-ai  abc-bi   abc-cs   xyz-testing   xyz-development --------------------------------------------------------------------------- 1        delhi      5000             4000     2000                          2        mumbai     3000     3000                           1000 --------------------------------------------------------------------------- 

the problem each new department in ref_department corresponds new column in view/select query.

i've written below query:

variable departments varchar2(100) begin :departments :=null;   cur_rec in (select distinct dept_desc ref_department) loop     :departments := :departments || ''''|| cur_rec.dept_desc|| '''' || ',' ;   end loop;   dbms_output.put_line(rtrim(:departments,','));  select * (select uid,uloc,uyear, depdesc, emp  (select c.unit_id uid, c.unit_loc uloc,c.year uyear, e.dept_id depid,r.dept_name depname,r.dept_desc depdesc,s.no_of_emp emp unit u inner join employees e on u.unit_id=e.unit_id inner join ref_department r on  e.dept_id=r.dept_id)) pivot (min(emp) depdesc in (:departments)); end; 

error: pl/sql: ora-56901: non-constant expression not allowed pivot|unpivot

i've referred below links: pivoting rows columns dynamically in oracle; http://www.orafaq.com/forum/t/187172/

long ago (before oracle's pivot queries existed) wrote blog post on "pivot" queries gives code package construct such queries.

for requirement this:

declare    rc sys_refcursor; begin    rc := pivot.pivot_cursor                ( group_cols   => 'u.unit_id, u.unit_loc'                , pivot_col    => 'rd.dept_desc'                , tables       => 'companyunit x, employees e, ref_departmnent rd'                , value_cols   => 'e.no_of_emp'                , agg_types    => 'sum'                , where_clause => 'e.dept_id = rd.dept_id , e.unit_id = c.unit_id'                 ); end; / 

since ref cursor can have variable number of columns need use dbms_sql package parse it, find out column names, , run it. starting point convert ref cursor dbms_sql cursor:

n := dbms_sql.to_cursor_number(rc); 

you'd need refer dbms_sql package documentation example take further.