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 |
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