| 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; |
| For static pivoting (to get output as shown above), refer to script |
74124