CODEWITHSUNDEEP
sqloracle-databaseoracle11gpivotprocedure
Amine Le
Amine Le

Reputation: 35

Oracle Pivot: from rows into columns

Hello is it possible to turn row values into columns. I am using ORACLE SQL and I want to take the month and turn it into columns with the kpi value as shown below. I tried partitions and merge statements but nothing seems to work. I would really appreciate some help. Thank you in advance.

Input data:

department year month kpi value
A 2000 1 sales 5000
A 2000 1 revenue per client 120
A 2000 2 sales 6000
A 2000 2 revenue per client 140

Desired Output:

department year kpi 1 2
A 2000 sales 5000 6000
A 2000 revenue per client 120 140

Upvotes: 3

Views: 89

Answers (2)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15905

You can use pivot to do so:

Schema and insert statements:

 create table mytable (department varchar(20),year int,month int,kpi varchar(50),value int);

 insert into mytable values('A',    2000,   1,  'sales' ,5000);
 insert into mytable values('A',    2000,   1,  'revenue per client',   120); 
 insert into mytable values('A',    2000,   2,  'sales' ,6000);
 insert into mytable values('A',    2000,   2,  'revenue per client',   140);

Query:

 select * from (
 select department,year,month,kpi,value
 from mytable
 )
 pivot
 (
     max(value)
     for month  in (1,2)
 )

Output:

DEPARTMENT YEAR KPI 1 2
A 2000 revenue per client 120 140
A 2000 sales 5000 6000

db<fiddle here

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1271231

You can use conditional aggrwegation:

select department, year, kpi,
       max(case when month = 1 then value end) as month_1,
       max(case when month = 2 then value end) as month_2
from t
group by department, year, kpi;

Upvotes: 1

Related Questions