Convert multiple columns into rows

Question

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!

This is actually an unpivot not a pivot. Unpivot converts multiple columns into rows. Pivot does the opposite. Since you want to unpivot "pairs" of columns, IMO the easiest way would be union. — Taryn
– Taryn, Commented Feb 24, 2015 at 20:55
And this problem is why you don't structure the table like that in the first place. You have the same problem if the question is "which client(s) have the phone number 12345678?" as you have to explicitly check each of the columns. And woe be to you if that column(s) is needed in a join (though that is unlikely). Also this is inflexible if you need to store more numbers or email addresses. — Turophile
– Turophile, Commented Feb 24, 2015 at 21:27

Aramillo · Accepted Answer · 2015-02-24 21:46:53Z

1

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

answered Feb 24, 2015 at 21:46

Aramillo

3,2263 gold badges27 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert multiple columns into rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related