Converting two column values in single column in Oracle SQL

Question

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?

Gordon Linoff · Accepted Answer · 2018-11-08 14:19:05Z

3

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.

answered Nov 8, 2018 at 14:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Robert Kock Over a year ago

Surely much better than my answer (which I just deleted).

ABR Over a year ago

Thanks for this solution. Which one has better performance your query or one with union all ?

Gordon Linoff Over a year ago

@ABR . . . In general, this will scan your table only once, which should be a performance gain. That said, scanning the table twice is really not a big deal, because it should already be cached for the second scan.

user5683823 · Accepted Answer · 2018-11-08 14:34:47Z

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

Collectives™ on Stack Overflow

Converting two column values in single column in Oracle SQL

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related