Oracle中的行转列实例

select optorname,
NVL(现金, 0) as 现金,
NVL(银行卡, 0) as 银行卡,
NVL(会员卡, 0) as 会员卡,
NVL(礼品卡, 0) as 礼品卡,
NVL(微信, 0) as 微信,
NVL(支付宝, 0) as 支付宝,
NVL(会员, 0) as 会员,
nvl(券, 0) as 券,
nvl(电子商务, 0) as 电子商务,
NVL(现金, 0) +NVL(银行卡, 0)+ nvl(会员卡, 0)+NVL(礼品卡, 0)+NVL(微信, 0)+NVL(支付宝, 0)+NVL(会员, 0) + nvl(券, 0)+ nvl(电子商务, 0) as 合计
from (select tor.operatorname||'('||tor.operatorcode||')' as optorname, p.paysum, p.paytypecode
from tkt_trademain t
left join tkt_tradepaytype p
on t.tradeid = p.tradeid
left join sys_operator tor
on t.optorcode=tor.operatorcode
where t.tradedate >= to_date(#MBTRADEDATE#,'yyyy-MM-dd HH24:Mi:ss')
and t.tradedate <= to_date(#METRADEDATE#,'yyyy-MM-dd HH24:Mi:ss')
order by t.optorcode desc) pivot(sum(paysum) for paytypecode in('01' 现金,
'05' 银行卡,
'22' 会员卡,
'21' 礼品卡,
'19' 微信,
'18' 支付宝,
'20' 会员,
'23' 券,
'07' 电子商务))