我得到如下 SQL 結果-
select stud_id, fee_cycle, amnt, curr from tab_a;
然后得到低于結果-
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
.........................................
所以我想得到如下 SQL 的結果 -
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
您能否幫我生成 SQL 查詢，以便我能夠得到像上面這樣的結果。
uj5u.com熱心網友回復：
嘗試如下
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
演示
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/425309.html