CODEWITHSUNDEEP
sqlsql-serverselect
Christopher Chase
Christopher Chase

Reputation: 2890

SQL Select multiple distinct column rows as one column

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

Upvotes: 1

Views: 304

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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

Upvotes: 3

Robert Martin
Robert Martin

Reputation: 17157

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

Upvotes: 6

Related Questions