CODEWITHSUNDEEP
sqloracle-database
Piyush Singh
Piyush Singh

Reputation: 57

SQL to select data in multiple row based on column

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

Upvotes: 2

Views: 68

Answers (3)

Kristina Lex
Kristina Lex

Reputation: 119

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

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522635

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

Upvotes: 0

Fahmi
Fahmi

Reputation: 37483

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)
)

Upvotes: 1

Related Questions