I am getting SQL result like below-
select stud_id, fee_cycle, amnt, curr from tab_a;
Then getting below result-
stud_id fee_cycle Amnt Curr
------- --------- ---- ----
1002 JUL_2021 125 USD
1004 DEC_2021 145 USD
1005 JAN_2022 150 USD
1007 FEB_2022 155 USD
1004 JAN_2022 150 USD
1007 NOV_2021 140 USD
.........................................
So I want to get result of SQL like below -
Stud_ID JUL_2021 AUG_2021 SEP_2021 OCT_2021 NOV_2021 DEC_2021 JAN_2022 FEB_2022 TOTAL_AMNT
------- -------- -------- -------- -------- -------- -------- -------- -------- --------
1002 125 125
1004 145 150 295
1005 150 150
1007 140 155 295
Could you please help me to generate SQL query so that i can able to get result like above.
CodePudding user response:
try like below
with ct
as
(
select 1002 as id, 'jul21' as m, 125 as amt from dual
union all
select 1002,'dec22',125 from dual
union all
select 1004,'DEC_2021',145 from dual
union all
select 1004,'JAN_2022',150 from dual
)
, p as (select * from
ct
pivot (
max(amt)
for m in ('jul21' as jul21,'dec22' as dec22,'DEC_2021' as DEC_2021,'JAN_2022'as JAN_2022)
)
) select p.*, nvl(jul21,0) nvl(dec22,0) nvl(DEC_2021,0) nvl(DEC_2021,0) as total from p