SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

help on pivot to substitute 0 value to a null

Warren TolentinoOct 25 2017 — edited Oct 31 2017

on this query using a pivot how can I substitute a 0 value to a nulls? thanks.

select *

from (select '102034' mid, '2015' fyr, 51885334.33 pmt from dual union all

select '130000' mid, '2015' fyr, 8815676.42 pmt from dual union all

select '130386' mid, '2015' fyr, 2718094.18 pmt from dual union all

select '102034' mid, '2016' fyr, 95746494.85 pmt from dual union all

select '130000' mid, '2016' fyr, 4746454.75 pmt from dual union all

select '130386' mid, '2016' fyr, 2717724.76 pmt from dual union all

select '102034' mid, '2017' fyr, 22619702.28 pmt from dual union all

select '130000' mid, '2016' fyr, 5659881.46 pmt from dual) cpy

pivot (sum(nvl(cpy.pmt,0)) for fyr in (2010,2011,2012,2013,2014,2015,2016,2017))

MID 2010 2011 2012 2013 2014 2015 2016 2017

------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------

102034 51885334.3 95746494.8 22619702.2

130000 8815676.42 10406336.2

130386 2718094.18 2717724.76

expected output:

MID 2010 2011 2012 2013 2014 2015 2016 2017

------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------

102034 0 0 0 0 0 51885334.3 95746494.8 22619702.2

130000 0 0 0 0 0 8815676.42 10406336.2 0

130386 0 0 0 0 0 2718094.18 2717724.76 0

Locked Post

New comments cannot be posted to this locked post.

Locked on Nov 28 2017

Added on Oct 25 2017

13 comments

699 views