Home > OS >  Get result of SQL in aggregated format along with distinct column value
Get result of SQL in aggregated format along with distinct column value

Time:02-17

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

demo

  • Related