Pivoting row dynamically in oracle database

Question

below is the scenario:

(* denotes primary key)
**CompanyUnit**(*Unit_id, unit_loc,Year);

**Employees**(Unit_id,Dept_id,no_of_emp); (unit_id and dept_id are 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: A 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 is that each new department in ref_department corresponds to a new column in view/select query.

I've written below query:

variable DEPARTMENTS VARCHAR2(100)
BEGIN
:DEPARTMENTS :=NULL;
  FOR cur_rec IN (SELECT DISTINCT DEPT_DESC FROM REF_DEPARTMENT) LOOP
    :DEPARTMENTS := :DEPARTMENTS || ''''|| cur_rec.DEPT_DESC|| '''' || ',' ;
  END LOOP;
  dbms_output.put_line(rtrim(:DEPARTMENTS,',')); 
select * from
(select uid,uloc,uyear, depdesc, emp from 
(select C.unit_id as uid, C.unit_loc as uloc,C.year as uyear, E.DEPT_ID as depid,R.DEPT_NAME as depname,R.DEPT_DESC as depdesc,S.NO_OF_EMP as emp
FROM 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) for depdesc in (:departments));
END;

error: PL/SQL: ORA-56901: non-constant expression is not allowed for pivot|unpivot

I've referred below links: Pivoting rows into columns dynamically in Oracle; http://www.orafaq.com/forum/t/187172/

You can do it absolutely dynamically, _if_ you are using `PIVOT XML`, then you can use `IN (ANY)`. Otherwise, you have to construct the pivoting query dynamically each and every time, then execute that select. Not the easiest :-) — Koshinae, Jun 01 '15 at 07:26
Thanks @Koshinae, I have checked this solution, but the issue is if I use XML then I need to process XML in my java code, which will eat some memory and time as well, as I need to export all the records (more than 15k records with 40+ columns) from the generated view(includes multiple joins) to an excel file. For this reason, I am looking for second approach. — Mohit, Jun 01 '15 at 10:06

score 1 · Accepted Answer · answered Jun 01 '15 at 10:56

Long ago (before Oracle's PIVOT queries existed) I wrote a blog post on "Pivot" Queries that gives you the code for a package to help construct such queries.

For your requirement you would do something like 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 and e.unit_id = c.unit_id' 
               );
end;
/

Since the ref cursor can have a variable number of columns you would need to use the DBMS_SQL package to parse it, find out the column names, and run it. The starting point would be to convert the ref cursor to a DBMS_SQL cursor:

n := dbms_sql.to_cursor_number(rc);

You'd need to refer to the DBMS_SQL package documentation example to take this further.

Pivoting row dynamically in oracle database

1 Answers1