pivot 行转列的使用

select rq as "日期", nvl(s, 0) as "网上银行", nvl(b, 0) as "现金", nvl(c, 0) as "POS机刷卡",nvl(s, 0)+nvl(b, 0)+nvl(c, 0) as "小计"
from (select SFJE, SKLXID, to_char(CREATEDTIME, 'yyyy-mm') as rq from T_CBJFPJ)
    pivot (sum(SFJE)
    for SKLXID in ('2c9081e740c261b20140c2918011000c' as s, 'F986EE84C5901576E04010AC0C052577' as b, 'F986EE84C5901576E04010AC0C053577' as c))
order by rq desc;

让Null 的数据显示为0 ，并且对每一行的数据进行相加

如果不想使用该方法去做行转列，可以使用  decode 方法进行行转列，

decode 方法参数

   1：表示条件

   2： 表示条件相等的值

   3： 表示的是 如果条件跟值相等，则返回

   4： 如果条件跟值不相等，则返回

(select to_char(CREATEDTIME,'yyyy-mm') rq,sum(decode(SKLXID,'2c9081e740c261b20140c2918011000c',sfje,0)) as wsyh,sum(decode(SKLXID,'F986EE84C5901576E04010AC0C052577',sfje,0)) as cash,sum(decode(SKLXID,'F986EE84C5901576E04010AC0C053577',sfje,0)) as posh
from t_cbjfpj where DELETEDFLAG ='0'
group by to_char(CREATEDTIME,'yyyy-mm'))
union


(select '合计',sum(decode(SKLXID,'2c9081e740c261b20140c2918011000c',sfje,0)) as wsyh,sum(decode(SKLXID,'F986EE84C5901576E04010AC0C052577',sfje,0)) as cash,sum(decode(SKLXID,'F986EE84C5901576E04010AC0C053577',sfje,0)) as posh
from t_cbjfpj where DELETEDFLAG ='0')  ;