Hi Tom,
I have a table structure in the database like below,
<u>Loc | BU | Curr | Product | Stages | Amount</u>
288 |30074| XCD | CCC | 1 | 21
288 |30074| XCD | OTH | 1 | 41
288 |30074| XCD | RMM | 1 | 10
288 |30074| XCD | RMM | 2 | 50
288 |30074| XCD | OTH | 2 | 30
288 |30074| XCD | CCC | 3 | 10
288 |30074| XCD | OTH | 3 | 30
488 |23435| CAD | RMM | 1 | 10
I need to display the values as
--------------------------------------------------------------------------------------
<u>Loc | Stage1 | Stage2 | Stage3 | ALL Stages |</u>
<u> |CCC|OTH|RMM|TOTAL|CCC|OTH|RMM|TOTAL|CCC|OTH|RMM|TOTAL|CCC|OTH|RMM|TOTAL |</u>
--------------------------------------------------------------------------------------
288 |21 |41 |10 |72 |0 |30 |50 |80 |10 |30 |0 |40 |31 |101 |60 |192 |
488 |0 |0 |10 |10 |0 |0 |0 |0 |0 |0 |0 |0 |0 |0 |10 |10 |
--------------------------------------------------------------------------------------
Please let me know, is there any way to display the record as above using pivot tables.
Thank you,
Karthik
So, you need to get:
- The product total for each stage
- The overall total for each stage
- The overall total for each product
- The grand total
And display these as columns?
If so, grouping sets is your friend. You can use this to get all the subtotals you want. And grouping_id() to identify which subtotal you have.
Once you've generated these, pivot the results for each (stage, prod) pair:
create table t (
loc int, bu int, curr varchar2(3),
prod varchar2(3), stages int, amount int
);
insert into t values (288 ,30074, 'XCD' , 'CCC' , 1 , 21);
insert into t values (288 ,30074, 'XCD' , 'OTH' , 1 , 41);
insert into t values (288 ,30074, 'XCD' , 'RMM' , 1 , 10);
insert into t values (288 ,30074, 'XCD' , 'RMM' , 2 , 50);
insert into t values (288 ,30074, 'XCD' , 'OTH' , 2 , 30);
insert into t values (288 ,30074, 'XCD' , 'CCC' , 3 , 10);
insert into t values (288 ,30074, 'XCD' , 'OTH' , 3 , 30);
insert into t values (488 ,23435, 'CAD' , 'RMM' , 1 , 10);
with rws as (
select loc, --prod,--stages,
case
when grouping_id ( prod ) = 1 THEN 'total'
else prod
end prod,
sum(amount) amount,
case
when grouping_id ( stages ) = 1 THEN 'total'
else to_char(stages)
end grp
from t
group by grouping sets ( (loc, curr, stages, prod), ( loc, prod ), ( loc, stages), loc )
)
select *
from rws
pivot (
sum(amount) for (grp, prod) in (
(1, 'CCC') ccc_1, (1, 'OTH') oth_1, (1, 'RMM') rmm_1, (1, 'total') tot_1,
(2, 'CCC') ccc_2, (2, 'OTH') oth_2, (2, 'RMM') rmm_2, (2, 'total') tot_2,
(3, 'CCC') ccc_3, (3, 'OTH') oth_3, (3, 'RMM') rmm_3, (3, 'total') tot_3,
('total', 'CCC') ccc_tot, ('total', 'OTH') oth_tot, ('RMM', 'total') rmm_tot,
('total', 'total') grand_tot
)
);
LOC CCC_1 OTH_1 RMM_1 TOT_1 CCC_2 OTH_2 RMM_2 TOT_2 CCC_3 OTH_3 RMM_3 TOT_3 CCC_TOT OTH_TOT RMM_TOT GRAND_TOT
288 21 41 10 72 <null> 30 50 80 10 30 <null> 40 31 101 <null> 192
488 <null> <null> 10 10 <null> <null> <null> <null> <null> <null> <null> <null> <null> <null> <null> 10
And nvl/coalesce the null columns to zero in your select.
You can find a similar example at:
https://asktom.oracle.com/pls/asktom/f?p=100:11:::NO:RP:P11_QUESTION_ID:9537350100346426892