SQL Simple Join and I'm at a Dead End

Question

I have a table with columns:

JOB_NUM, HM_PH, BUS_PH, CALL1ST 

Where job_num is a unique number

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

So, using the order of the columns above, the data samples will look like this:

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

I want to create 2 columns.

 JOB_NUM, PHONE 

If job_num is listed next to each phone number, for example:

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

Where to begin?

Answer 1

You need 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 UNION ALL for all the numbers you need (with duplicates) or UNION when you need unique lines:

 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