How to pivot below Text data in Oracle?

Question

*Input Table : How to get data from two diff columns into one single column in oracle .

name   age   city    state
 A      25    CAL     WB
 B      27    PAT     BR
 B      38    HOW     WB

*output Table

 name   age   place
  A      25     CAL
  A      25     WB
  B      27     PAT
  B      27     BR
  B      38     HOW
  B      38     WB

Since you know that it is (un)pivot, what did you try so far and what was wrong with it? What is the result of your research? — astentx, Jul 28 '21 at 09:40
All the examples and scenarios were explained using some aggregation on numbers, I wanted pivot using all text data(I got stuck at this part) — jateen kashyap, Jul 28 '21 at 11:45

score 1 · Answer 1 · answered Jul 28 '21 at 10:31

And you can use a lateral join:

select t.name, age, x.place
from input t cross join lateral
     (select t.city as place from dual union all
      select t.state as place from dual
     ) x;

I also think that unpivoting provides a nice gentle introduction to lateral joins, which are quite powerful and useful in many other contexts (as opposed to the unpivot syntax which is not very general).

score 0 · Accepted Answer · answered Jul 28 '21 at 09:28

0

You can use union all :

select name, age, city as place
from t 
union all
select name, age, state as place
from t;

answered Jul 28 '21 at 09:28

Yogesh Sharma

49,870
5
26
52

Hi yogesh, Could you please tell me why to use Union all instead of Union in this example? – jateen kashyap Jul 28 '21 at 11:47

score 0 · Answer 3 · answered Jul 28 '21 at 09:36

The UNPIVOT clause works perfectly for that purpose.

with t (name, age, city, state) as (
select 'A', 25, 'CAL', 'WB' from dual union all
select 'B', 27, 'PAT', 'BR' from dual union all
select 'B', 38, 'HOW', 'WB' from dual
)
select name, age, place, type
from t
unpivot (
place for type in (
  city as 'city'
, state as 'state' 
  )
)
;

How to pivot below Text data in Oracle?

3 Answers3