Oracle将行转化为列显示输出

/*
场景：将行转化为列显示输出
函数1：pivot （）子句
语法：

select * from table
pivot(
pivot_clause,
pivot_for_clause,
piovt_in_clause
)
;

PIVOT 子句包含 3 个部分：

pivot_clause，定义需要汇总的数据，也就是聚合函数。例如使用 SUM(qbxssr) 汇总金额；
pivot_for_clause，指定需要从行转换成列的字段。例如使用 for yf 将每个月的数据显示为一列；
pivot_in_clause，指定将 pivot_for_clause 字段中的哪些数据值转换为列。例如 in (’201901’, ‘201902’) 表示只将 201901 和 201902 两个月份的数据转换为列。

函数2：coalesce（valu1，valu2）
coalesce 函数用于将汇总行中的 NULL 值显示为相应的信息。查询返回的结果如下：配合高级分组使用
*/

–统计纳税人申报增值税小规模申报表每月的全部销售收入（一般劳务/服务不动产）

with yfmx as
 (select a.nsrdzdah, to_char(sssqq, 'mm') yf, sum(a.qbxssr) qbxssr
    from SB_ZZS_XGM A
   where A.ZQ_ID LIKE '2019%'
     and a.gzlx_id in('1','4','5')
     and a.yxsbbz='Y'
     AND A.YXBZ='Y'
     AND A.NSRDZDAH in ('10115201000083028443', '10115201000083031222')
     and a.lxh in( '1','2')
   group by rollup(to_char(sssqq, 'mm'), a.nsrdzdah)
  
  ),
 result_list  as
 (select *
    from yfmx
  pivot(sum(qbxssr)
     for yf in('01' as y1,
              '02' as y2,
              '03' as y3,
              '04' as y4,
              '05' as y5,
              '06' as y6,
              '07' as y7,
              '08' as y8,
              '09' as y9,
              '10' as y10,
              '11' as y11,
              '12' as y12)))
select coalesce(nsrdzdah, '合计') as "纳税人电子档案号",
       y1 "一月份",
       y2 "二月份",
       y3 "三月份",
       y4 "四月份",
       y5 "五月份",
       y6 "六月份",
       y7 "七月份",
       y8 "八月份",
       y9 "九月份",
       y10 "十月份",
       y11 "十一月份",
       y12 "十二月份",
       (y1 + y2 + y3 + y4 + y5 + y6 + y7 + y8 + y9 + y10 + y11 + y12) as "总计"
  from result_list
 order by nsrdzdah;