SQL to select data in multiple row based on column

user_5090523 0
Viewed 57

I want to write a Oracle sql query which will fetch data in below order:

NAME    Phone1  Phone2
JOHN    1123     321
RIK 11  Null
KATE    Null    21

Output:

NAME    PHONE
JOHN    1123
JOHN    321
RIK 11
KATE    21
oracle sql
3 Answers

You can try using UNION ALL

select name, phone1 as phone from tablenmae
union all
select name, phone2  from tablenmae

OR You can try using UNPVOT

select *
  from tablenmae
unpivot
(
    phone
    for val in (phone1, phone2)
)
user_69148640

We can try using UNION ALL here:

SELECT NAME, Phone1 AS PHONE FROM yourTable WHERE Phone1 IS NOT NULL
UNION ALL
SELECT NAME, Phone2 FROM yourTable WHERE Phone2 IS NOT NULL
ORDER BY NAME, PHONE;

enter image description here

Demo

user_18632290

select name,phone1 from table1 where phone1 is not null union
select name,phone2 from table1 where phone2 is not null

user_37897590
Related