所以這是我的示例資料：
uj5u.com熱心網友回復：
我認為您不需要使用任何rollup功能。我很確定也許有更好更優雅的解決方案，但要獲得您想要的輸出，您可以使用它
提示：我必須使用子選擇來擺脫從資料透視出來的列名。
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
資料庫<>小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/338677.html
上一篇：CLOB資料型別超出限制