关于pivot行转列后的列null值转为0的问题

假设现在有一张每月收入的表

create table shouru( yf varchar2(10) ,sr varchar2(50));
insert into shouru(yf, sr) values('01','12345');
insert into shouru(yf, sr) values('02','45678');
insert into shouru(yf, sr) values('06','99999');
           

现在需要用pivot查询一年中每个月的收入并要显示1-12每个月，通常我们会这样写

select
*
from (select
         yf, --月份
         sr  --收入
         from shouru)
pivot(max(sr)
   for yf in('01' "01",
             '02' "02",
             '03' "03",
             '04' "04",
             '05' "05",
             '06' "06",
             '07' "07",
             '08' "08",
             '09' "09",
             '10' "10",
             '11' "11",
             '12' "12"))
           

这样就有个问题，表中不存在的月份的sr就会为null，有的人尝试在pivot的聚合函数内使用nvl函数，像这样max(nvl(sr，0)),但是结果还是一样。用下面的方法可以解决这个问题。

select
nvl(t."01",0) "01",
nvl(t."02",0) "02",
nvl(t."03",0) "03",
nvl(t."04",0) "04",
nvl(t."05",0) "05",
nvl(t."06",0) "06",
nvl(t."07",0) "07",
nvl(t."08",0) "08",
nvl(t."09",0) "09",
nvl(t."10",0) "10",
nvl(t."11",0) "11",
nvl(t."12",0) "12",
from (select
         yf, --月份
         sr  --收入
         from shouru )
pivot(max(sr)
   for yf in('01' "01",
             '02' "02",
             '03' "03",
             '04' "04",
             '05' "05",
             '06' "06",
             '07' "07",
             '08' "08",
             '09' "09",
             '10' "10",
             '11' "11",
             '12' "12")) t
           

第一次写博客，有不对的地方希望大家指正