Search code examples
sqloracle-databasepivotunpivot

Convert multiple columns into rows

SELECT ID, PHONE1, PHONE2, PHONE3, EMAIL1, EMAIL2, EMAIL3, EMAIL4
FROM (SELECT ...)

How do I pivot it into this? 

*ID | PHONE | EMAIL*

SELECT DISTINCT ID, PHONE, EMAIL
FROM ...

I can do multiple selects and UNION them but I wonder if there is a better way of doing it. My query is like 400 lines long and doing multiple unions will be a mess!

Solution

  • As bluefeet said for converts columns into rows you need to use unpivot. In your case you should do unpivot twice, for phone and email. Something like:

    SELECT id,phone,email FROM (
select id,phone,EMAIL1, EMAIL2, EMAIL3, EMAIL4 from ...
unpivot (
phone for p1 
in (PHONE1, PHONE2, PHONE3)
))
unpivot (
email for e1
in (EMAIL1, EMAIL2, EMAIL3, EMAIL4)
);