sqloracleoracle11gpivotprocedure

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

Solution

  • 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