i have this query below that I would like to use the pivot so the year values can be convert as the amount columns.
select *
from (
select ivca.*, ivcp.*
from (select ca.muni ca_muni_id,
ca.fy ca_fy,
sum(ca.amount) allot_amt
from chips.allot ca
where ca.type = '1' -- capital allotment for the given fy
and ca.cycle = '1' -- initial allotment for the given fy
and ca.fy between 2015 and 2017
and ca.muni in ('002034','030000','030386')
group by ca.muni, ca.fy
order by ca.fy, ca.muni) ivca, -- inline view for allotment
(select ivpr.muni_id cp_muni_id,
ivpr.fy cp_fy,
sum(ivpr.pay_amt) pay_amt
from (select cr.muni muni_id,
cr.fy fy,
sum(cr.amount) pay_amt
from chips.request cr
where cr.pay_id is not null
group by cr.muni, cr.fy) ivpr -- inline view payment request
where ivpr.fy between 2015 and 2017
and muni_id in ('002034','030000','030386')
group by ivpr.muni_id, ivpr.fy
order by ivpr.fy, ivpr.muni_id ) ivcp -- inline view for payment
where ivca.ca_muni_id = ivcp.cp_muni_id
and ivca.ca_fy = ivcp.cp_fy ) ivap -- inline view for allotment and payment
output:
CA_MUNI_ID CA_FY ALLOT_AMT CP_MUNI_ID CP_FY PAY_AMT
---------- ----- ---------- ---------- ----- ----------
002034 2015 85971408.7 002034 2015 51885334.3
030000 2015 6960770.25 030000 2015 8815676.42
030386 2015 2718094.18 030386 2015 2718094.18
002034 2016 85971408.7 002034 2016 95746494.8
030000 2016 6950542.54 030000 2016 4746454.75
030386 2016 2717724.76 030386 2016 2717724.76
002034 2017 85971408.7 002034 2017 22619702.2
030000 2017 6946665.48 030000 2017 5659881.46
030386 2017 2717951.21
9 rows selected
I was hoping that I could use the pivot to get this output:
CA_MUNI_ID 2015 2016 2017 CP_MUNI_ID 2015 2016 2017
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
002034 85971408.7 85971408.7 85971408.7 002034 51885334.3 95746494.8 22619702.2
030000 6960770.25 6950542.54 6946665.48 030000 8815676.42 4746454.75 5659881.46
030386 2718094.18 2717724.76 2717951.21 030386 2718094.18 2717724.76
I tried this
select *
from (
select ivca.*, ivcp.*
from (select ca.muni ca_muni_id,
ca.fy ca_fy,
sum(ca.amount) allot_amt
from chips.allot ca
where ca.type = '1' -- capital allotment for the given fy
and ca.cycle = '1' -- initial allotment for the given fy
and ca.fy between 2015 and 2017
and ca.muni in ('002034','030000','030386')
group by ca.muni, ca.fy
order by ca.fy, ca.muni) ivca, -- inline view for allotment
(select ivpr.muni_id cp_muni_id,
ivpr.fy cp_fy,
sum(ivpr.pay_amt) pay_amt
from (select cr.muni muni_id,
cr.fy fy,
sum(cr.amount) pay_amt
from chips.request cr
where cr.pay_id is not null
group by cr.muni, cr.fy) ivpr -- inline view payment request
where ivpr.fy between 2015 and 2017
and muni_id in ('002034','030000','030386')
group by ivpr.muni_id, ivpr.fy
order by ivpr.fy, ivpr.muni_id ) ivcp -- inline view for payment
where ivca.ca_muni_id = ivcp.cp_muni_id
and ivca.ca_fy = ivcp.cp_fy ) ivap -- inline view for allotment and payment
pivot (sum(nvl(ivap.allot_amt,0)) for ca_fy in (2015,2016,2017)),
sum(nvl(ivap.pay_amt,0)) for cp_fy in (2015,2016,2017))
gives me error:
ORA-00933: SQL command not properly ended
I would like to use two columns in the pivot at the same time. is there a work around? please help. thank you.
the reason why I have to use a pivot is because the columns are unknown depending on the input year.