CODEWITHSUNDEEP
sqloracle-databaseunpivot
d.subhankar
d.subhankar

Reputation: 1

Convert Rows into Cols

I have the following table call fc_summary:

CALMONTH   COMP_CODE    FC10    FC20    FC30
201901     C001         550      45     100
201901     C002         50       450    10
201901     C003         150      250    120
201901     C004         520      410    10
201901     C005         30       40     105

From it I would like to create my_summary table like

CALMONTH   CODE     AMOUNT
201901     FC10     1300
201901     FC20     1195
201901     FC30     345

I tried to achieve this using PIVOT but could not figure it

Upvotes: 0

Views: 53

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65158

You can use this query 

with t(CALMONTH, COMP_CODE, FC10, FC20, FC30) as
(
 select 201901,'C001',550,45,100  from dual union all
 select 201901,'C002',50,450,10   from dual union all
 select 201901,'C003',150,250,120 from dual union all
 select 201901,'C004',520,410,10  from dual union all
 select 201901,'C005',30,40,105   from dual
)
select CALMONTH, FC as CODE, SUM(VALUE) as VALUE
  from t
 unpivot 
 (
  VALUE for FC in (FC10,FC20,FC30)
 )  
 group by CALMONTH, FC
 order by FC;

CALMONTH    CODE    VALUE
201901      FC10    1300
201901      FC20    1195
201901      FC30    345

to unpivot your data

Upvotes: 1

Related Questions