SQL Simple Join and I'm stumped

Question

I have a table with columns:

JOB_NUM, HM_PH, BUS_PH, CALL1ST

Where the job_num is a unique number

And the HM_PH, BUS_PH, and CALL1ST columns are 10 digit phone numbers

So using the order of columns above, sample data would look like:

JOB_NUM, HM_PH,      BUS_PH,     CALL1ST
------------------------------------
12345,   4025557848, 9165897588, 7518884455  
10101,   8887776655, 8667416895, 5558884446

What I want to produce is 2 columns.

JOB_NUM, PHONE

Where the job_num is listed next to every phone number such as:

JOB_NUM PHONE
---------------------
12345   4025557848  
12345   9165897588  
12345   7518884455  
10101   8887776655  
10101   8667416895  
10101   5558884446  

Where do I start?

Answer 1

You need a UNION (if you want to remove duplicate rows) or UNION ALL (if you want to keep duplicate rows):

SELECT JOB_NUM, HM_PH AS PHONE FROM yourtable
UNION
SELECT JOB_NUM, BUS_PH FROM yourtable
UNION
SELECT JOB_NUM, CALL1ST FROM yourtable
ORDER BY JOB_NUM

Answer 2

Make a UNION ALL for all numbers you need (with duplicates) or UNION when you need unique rows:

select JOB_NUM,HM_PH AS PHONE
from YourTableName
union all
select JOB_NUM,BUS_PH AS PHONE
from YourTableName
union all
select JOB_NUM,CALL1ST_PH AS PHONE
from YourTableName

Answer 3

create table yourtable
(
    id int,
    HM_PH nvarchar(10),
    BUS_PH nvarchar(10),
    CALL1ST nvarchar(10)
)


insert into yourtable
select 12345,   4025557848, 9165897588, 7518884455  
union
select 10101,   8887776655, 8667416895, 5558884446



select * from yourtable

select ID,p.Phone
from temptemp
unpivot( Phone for phoneCol in (HM_PH,BUS_PH,CALL1ST)) p
order by id 

 drop table yourtable