Oracle Database: Pivot Report Query
The code below is an example of use of dynamic SQL in PL/SQL to get a pivot output in Oracle 10g. The version 11g has built-in function to create the same. This is based on a similiar code discussed by Tom Kyte on Oracle website asktom.com.
Pivoting can be done using the Oracle Model clause SQL Model Pivoting

CREATE OR REPLACE PROCEDURE get_year_month_pivot(o_rc OUT SYS_REFCURSOR) AS
v_query VARCHAR2(2000) := 'SELECT cal_year year,loc_id,';
BEGIN
FOR st_rec IN (SELECT DECODE(level, 1,'JAN',2,'FEB',3,'MAR',4,'APR',5,'MAY',6,'JUN', 7,'JUL',8,'AUG',9,'SEP',10,'OCT',11,'NOV',12,'DEC') mon_cd
FROM DUAL CONNECT BY level <=12) LOOP
v_query := v_query || REPLACE(q'|, SUM(DECODE(mon_cd,'$ST_REC$',sales)) $ST_REC$|',
'$ST_REC$',DBMS_ASSERT.simple_sql_name(st_rec.mon_cd));
END LOOP;
v_query := v_query ||' FROM fact_sales_details GROUP BY cal_year,loc_id';
OPEN o_rc FOR v_query;
EXCEPTION
WHEN OTHERS THEN RAISE;
END get_year_month_pivot;
/
Testing the procedure Output
VAR orc refcursor;
BEGIN get_year_month_pivot(:orc); END;
/
PRINT orc;

Procedure Output


For static pivoting (to get output as shown above), refer to script


Back


Oracle registered trademark of Oracle Corporation.
Last Revised On: 10/07/2013

  1253