Pivot Dynamic Data in Oracle

Question

I have rollup output of month-wise average sales of products in a quarter as shown below:

Rollup output:

Product    Month    Sales
------------------------------
Product1    MAY    101.27
Product2    MAY    5.47
Product1    JUN    1481.19
Product2    JUN    84.95
ALL         QTR    836.44

I need final output in the following format:

            Product1  Product2   AverageSales
May         101.27    1481.19    (null)
Jun         5.47      84.95      (null)
Jul         0         0          (null)    
ALL         (null)    (null)     836.44

I tried to apply pivot on month but since month name is a dynamic value based on the quarter selected, I tried to pivot on the product:

select * from (rollup output) 
PIVOT MIN(Sales) FOR Product IN ('Product1' AS Product1_sales, 'Product2' AS Product2_sales, 'ALL' AS Average Sales');

I received the following output:

MONTH  Product1_sales    Product2_sales    Average Sales
MAY    5.47                 (null)              (null)
MAY     (null)              101.27              (null)  
JUN    84.95                (null)              (null)
JUN     (null)              1481.19             (null)
Qtr     (null)              (null)              836.44

However, I need data in the final format mentioned above. How can I achieve this? Also, if data for a given month of the quarter is not available, I want to display its value as 0 (also shown in the final format above). How can I achieve that, considering that month name is dynamic?

Edit: I can pivot using product as well since I do not want xml output that comes out of dynamic pivot.

Possible duplicate of [Dynamic pivot in oracle sql](https://stackoverflow.com/questions/15491661/dynamic-pivot-in-oracle-sql) — Kamil Gosciminski, Aug 16 '18 at 11:09

score 4 · Answer 1 · answered Aug 16 '18 at 11:33

I'm not sure why it needs to be dynamic since the number of months is unlikely to change. Why not just:

select product
     , nvl(jan,0) as jan
     , nvl(feb,0) as feb
     , nvl(mar,0) as mar
     , nvl(apr,0) as apr
     , nvl(may,0) as may
     , nvl(jun,0) as jun
     , nvl(jul,0) as jul
     , nvl(aug,0) as aug
     , nvl(sep,0) as sep
     , nvl(oct,0) as oct
     , nvl(nov,0) as nov
     , nvl(dec,0) as dec
from   rollup_output
pivot  (min(sales) for (month) in
       ( 'JAN' as jan, 'FEB' as feb, 'MAR' as mar, 'APR' as apr, 'MAY' as may, 'JUN' as jun
       , 'JUL' as jul, 'AUG' as aug, 'SEP' as sep, 'OCT' as oct, 'NOV' as nov, 'DEC' as dec)
       );

SQL Fiddle

Pivot Dynamic Data in Oracle

1 Answers1