数据库:oracle 问题:在一张明细表里有一个字段表示类型,一个表示金额。现需要将几种类型的金额各合计为一种。 如现有如下数据: idtypeamount 1A100 1A20 1B20 1C10 2B150 2D50 3A50 3C10 3D20 ............ 变成 idtypyA_amounttypyB_amounttypyC_amountt
数据库:oracleselect temp.id ,sum(temp.typyA_amount) as typyA_amount ,sum(temp.typyB_amount) as typyB_amount ,sum(temp.typyC_amount) as typyC_amount ,sum(temp.typyD_amount) as typyD_amount from (select t.id ,sum(t.A) as typyA_amount --A ,null as typyB_amount --B ,null as typyC_amount --C ,null as typyD_amount --D from table t where t.type ='A' group by t.id union all select t.id ,null as typyA_amount --A ,sum(t.B) as typyB_amount --B ,null as typyC_amount --C ,null as typyD_amount --D from table t where t.type ='B' group by t.id union all select t.id ,null as typyA_amount --A ,null as typyB_amount --B ,sum(t.C) as typyC_amount --C ,null as typyD_amount --D from table t where t.type ='C' group by t.id union all select t.id ,null as typyA_amount --A ,null as typyB_amount --B ,null as typyC_amount --C ,sum(t.D) as typyD_amount --D from table t where t.type ='D' group by t.id) temp