CODEWITHSUNDEEP
oracle-databasepivot
prog_prog
prog_prog

Reputation: 391

pivot by 2 columns

I have a table with columns:

date_report    entity      value     code_column
12/02/2014       10         0.5       1
12/02/2014       10         333       2
12/02/2014       10         -44       3
12/02/2014       11         0.3       1    
12/02/2014       14         0.5       1
12/02/2014       11         777       2    
12/02/2014       14         -4        3
12/03/2015       10         0.4       1
12/03/2015       15         432       2  
10/01/2014       12         555       2

I would like to convert the rows to columns, when the "date_report" and the "entity" is like a key (they remains) and the code_column convert to name of new column and the value is the value in the new column. like:

date_report     entity       A         B       C
12/02/2014       10         0.5       333     -44
12/02/2014       11         0.3       777     
12/02/2014       14         0.5                -4
12/03/2015       10         0.4       
12/03/2015       15                   432
10/01/2014       12                   555

Upvotes: 0

Views: 68

Answers (2)

Hong Van Vit
Hong Van Vit

Reputation: 2976

You can try like this:

WITH TB_TAM AS(
  SELECT '12/02/2014' date_report,       10 entity,        0.5 valueT,      1 code_column  FROM DUAL UNION ALL
  SELECT '12/02/2014' date_report ,       10 entity,        333 valueT,      2 code_column FROM DUAL UNION ALL
  SELECT '12/02/2014' date_report ,       10 entity,        -44 valueT,      3 code_column FROM DUAL UNION ALL
  SELECT '12/02/2014' date_report ,       11 entity,        0.3 valueT,      1 code_column  FROM DUAL UNION ALL   
  SELECT '12/02/2014' date_report ,       14 entity,        0.5 valueT,      1 code_column  FROM DUAL UNION ALL
  SELECT '12/02/2014' date_report ,       11 entity,        777 valueT,      2  code_column  FROM DUAL UNION ALL  
  SELECT '12/02/2014' date_report ,       14 entity,        -4  valueT,      3 code_column FROM DUAL UNION ALL
  SELECT '12/03/2015' date_report ,       10 entity,        0.4 valueT,      1 code_column FROM DUAL UNION ALL
  SELECT '12/03/2015' date_report ,       15 entity,        432 valueT,      2  code_column  FROM DUAL UNION ALL
  SELECT '10/01/2014' date_report ,       12 entity,        555 valueT,      2 code_column  FROM DUAL 
)
select * from TB_TAM
PIVOT ( sum(valueT)for code_column in (1 as a,2 as b,3 as c)) 
order by date_report,entity

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can use conditional aggregation:

select date_report, entity,
       max(case when code_column = 1 then value end) as a,
       max(case when code_column = 2 then value end) as b,
       max(case when code_column = 3 then value end) as c
from t
group by date_report, entity
order by date_report, entity;

Upvotes: 1

Related Questions