CODEWITHSUNDEEP
sqldatabaseoracle-database
ABR
ABR

Reputation: 39

Converting two column values in single column in Oracle SQL

I have table like this below,

SNO     Name    Sales   Profit
1      John       50    20
2      Peter      60    0 
3      Mark       15    10
4      Nicolas      0  -10
5      Alex       70    20

I would like to convert this into below, (i.e sales and profit values should be combined to single column). 

SNO     Name     Sales_Profit Values
1      John        Sales      50
1      John        profit     20
2      Peter       Sales      60
2      Peter       Profit      0

and So on.

Is there any way to handle this scenario without functions/procedures?

Upvotes: 0

Views: 124

Answers (2)

user5683823
user5683823

Reputation:

Oracle introduced the unpivot operator in version 11.1, exactly for this kind of problem. Illustrated below. I create the sample data in a with clause (not part of the query to answer your question; you should remove it, and use the actual table name). Note that values is an oracle reserved word, so it shouldn't be used as a column name; I use value instead. And there should be no forward slashes in column names; I replaced with an underscore.

with
  base_data (sno, name, sales, profit) as (
    select 1, 'John'   , 50,  20 from dual union all
    select 2, 'Peter'  , 60,   0 from dual union all 
    select 3, 'Mark'   , 15,  10 from dual union all
    select 4, 'Nicolas',  0, -10 from dual union all
    select 5, 'Alex'   , 70,  20 from dual
  )
select  *
from    base_data
unpivot include nulls 
    (value for sales_profit in (sales as 'Sales', profit as 'Profit'))
;

 SNO NAME    SALES_PROFIT  VALUE
---- ------- ------------ ------
   1 John    Sales            50
   1 John    Profit           20
   2 Peter   Sales            60
   2 Peter   Profit            0
   3 Mark    Sales            15
   3 Mark    Profit           10
   4 Nicolas Sales             0
   4 Nicolas Profit          -10
   5 Alex    Sales            70
   5 Alex    Profit           20

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269493

One method uses cross join:

select t.sno, s.name, sp.salesprofit,
       (case when sp.salesprofit = 'Sales' then sales else profit end) as values
from t cross join
     (select 'Profit' as salesprofit from dual union all
      select 'Sales' from dual
     ) sp;

The advantage of this approach over a union all is that it should only scan the table once.

Upvotes: 3

Related Questions