SQL Select multiple distinct column rows as one column

Question

I'm not sure of a better way to word this, but say I have a table

name,    homephone,   fax,    mobile
========================================
bob,     123,         456,    999
chris,   null,        890,    null

I'm trying to create a SQL statement that will get me something like this

name,     phone
================
bob,      123
bob,      456
bob,      999
chris,    890

Robert Martin · Accepted Answer · 2011-09-05 06:35:17Z

6

How about using UNION? Say your table is called Directory. Something like:

SELECT name, homephone AS phone FROM Directory WHERE homephone IS NOT NULL
UNION
SELECT name, fax AS phone FROM Directory WHERE fax IS NOT NULL
UNION
SELECT name, mobile AS phone FROM Directory WHERE mobile IS NOT NULL

answered Sep 5, 2011 at 6:35

Robert Martin

17.3k17 gold badges65 silver badges89 bronze badges

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

Comments

Mikael Eriksson · Accepted Answer · 2011-09-05 06:52:06Z

3

select T.name, P.phone
from YourTale as T
  cross apply (
                select homephone union all
                select fax union all
                select mobile
              ) as P(phone)
where p.phone is not null

answered Sep 5, 2011 at 6:52

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

1 Comment

deutschZuid Over a year ago

+1 I prefer this over Robert's solution, but Robert's is excellent as well.

Collectives™ on Stack Overflow

SQL Select multiple distinct column rows as one column

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related