How to use rollup/cube in pivot table (SUMMARIZE ROWS)-CodePudding

So here is my sample data:

CodePudding user response：

I think you don't need to use any rollup function. I am quite sure that perhaps there is a better and more elegant solution, but to obtain the output you want you might use this

Tip: I had to use a subselect in order to get rid off the column names coming out of the pivot.

select 
b.* , 
coalesce(alcohol_pc,0) coalesce(food_pc,0) coalesce(notfood_pc,0) coalesce(cigarette_pc,0) as sum_pc,
coalesce(alcohol_hm,0) coalesce(food_hm,0) coalesce(notfood_hm,0) coalesce(cigarette_hm,0) as sum_hm
from 
(
select typ, 
       user_name,
       "'Alcohol'_PC" as alcohol_pc , 
       "'Alcohol'_HM" as alcohol_hm ,
       "'Food'_PC"    as food_pc ,
       "'Food'_HM"    AS food_hm , 
       "'NotFood'_PC" as notfood_pc ,
       "'NotFood'_HM" as notfood_hm ,
       "'Cigarette'_PC" as cigarette_pc ,
       "'Cigarette'_HM" as cigarette_hm 
from 
(
select
    typ,
    user_name,
    sort,
    count_pc,
    weight
from stat where typ=1 and dat>=trunc(sysdate)
)
pivot
(SUM(to_number(count_pc)) as pc, SUM(to_number(round(weight,0))) as hm
for sort in ('Alcohol','Food','NotFood' ,'Cigarette' ) )
order by user_name asc
) b

db<>fiddle