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
=======================开发者_如何学JAVA=================
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

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

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