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
Add a comment
|
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)
)
1 Comment
Tim Biegeleisen
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;
Demo
Comments
select name,phone1 from table1 where phone1 is not null
union
select name,phone2 from table1 where phone2 is not null
