SQL is used to classify amounts by type

Database: oracle problem: there is a field in a detail table that represents the type and one that represents the amount. You need to aggregate the amounts of several types into one. For example, the following data is available: idtypeamount1A1001A201B201C102B1502D503A503C103D20 ...... it is changed to idtypyA_amounttypyB_amounttypyC_amountt

Database: oracle problem: there is a field in a detail table that represents the type and one that represents the amount. You need to aggregate the amounts of several types into one. For example, the following data is available: idtypeamount 1A100 1A20 1B20 1C10 2B150 2D50 3A50 3C10 3D20 ...... it becomes idtypyA_amounttypyB_amounttypyC_amountt

Database: oracle

Problem: there is a field in a detail table that represents the type and a field that represents the amount. You need to aggregate the amounts of several types into one.

If the following data exists:

Id type amount

1 A 100

1 A 20

1 B 20

1 C 10

2 B 150

2 D 50

3 A 50

3 C 10

3 D 20

............

Change

Id typyA_amount typyB_amount typyC_amount typyD_amount

1 120 20 10

2 150 50

3 50 10 20



........

My implementation is to use union all to build a temporary table and then use aggregation. I would like to ask if there is any better way, so the speed should not be very fast. Please help me !!!! <无>

selecttemp.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_amountfrom   (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