oracle 利用case与聚合函数实现列转行

思想：

利用case when ,构建需要查询出的字段名称，然后利用聚合函数进行汇总。

SELECT SUBPOLICYNO, SUMGROSSPREMIUM, SUMNETPREMIUM, 
SUM(( case  WHEN FEE='R10' then PAYFEE ELSE 0 END))  我方费用,
SUM(( case  WHEN FEE='R10C' then PAYFEE ELSE 0 END)) 从方费用,
SUM(( case  WHEN FEE='R10' then COINSRATE ELSE 0 END)) 我方比例,
SUM(( case  WHEN FEE='R10C' then COINSRATE ELSE 0 END)) 从方比例,
MIN( case  WHEN FEE='R10' then COINSCODE ELSE NULL END) 我方代码,
MIN( case  WHEN FEE='R10' then PAYEENAME ELSE NULL END) 我方名称,
MIN( case  WHEN FEE='R10C' then COINSCODE ELSE NULL END) 从方代码,
MIN( case  WHEN FEE='R10C' then PAYEENAME ELSE NULL END) 从方名称
 FROM (
        SELECT  a.SUBPOLICYNO,SUMGROSSPREMIUM, SUMNETPREMIUM, decode(feetypecode,'R10Z','R10',feetypecode) fee , SUM(payfee) payfee,b.COINSRATE,b.COINSCODE,PAYEENAME
        FROM coredb.gppolicypaymentdetail a ,coredb.gupolicycopycoinsurance b,coredb.gupolicyrisk c
        WHERE a.subPOLICYNO = b.subPOLICYNO AND a.ENDORSESEQNO = b.ENDORSEQNO AND a.PAYEENAME = b.COINSNAME 
        AND a.subPOLICYNO=c.subPOLICYNO
        AND feetypecode IN('R10','R10Z','R10C','R10P')  
        AND a.subPOLICYNO IN(
        'Z131010080120180003256')
        GROUP BY a.SUBPOLICYNO,SUMGROSSPREMIUM, SUMNETPREMIUM, decode(feetypecode,'R10Z','R10',feetypecode),b.COINSRATE,b.COINSCODE,PAYEENAME
) GROUP BY  SUBPOLICYNO, SUMGROSSPREMIUM, SUMNETPREMIUM;

sql子查询截图：

列转行的效果图：