How to create separate rows for each unique value in source data

Question

I have following table:

Cus_ID  Work_Phone     Home_Phone     Mobile_Phone
1       x              Blank          x
2       x              x              Blank
3       x              x              x
.
.
. and so on (1000s of rows)

Work_Phone, Home_Phone, Mobile_Phone - varchar

x = some value present

I need to select from Source data to move it Target system like below, I need to create separate row for unique values for each Cus_ID. How do i do it?

Cus_ID    Type      ContactNo
1         Work       x
1         Mobile     x
2         Work       x
2         Home       x
3         Work       x
3         Home       x
3         Mobile     x

.. and so on

Type, ContactNo - varchar

x = Should be the corresponding value from Source table

Answer 1

Assuming col1, col2 and col3 are of the same type, then:

SELECT pk, col2 AS target_value FROM your_table WHERE col2 IS NOT NULL
UNION 
SELECT pk, col3 AS target_value FROM your_table WHERE col3 IS NOT NULL
UNION 
SELECT pk, col4 AS target_value FROM your_table WHERE col4 IS NOT NULL
ORDER BY pk

Edit edit: here's the version with ISNULL tests, column headings and the rest, in response to your revised question:

SELECT Cus_ID, 'Work' AS Type, Work_Phone AS ContactNo FROM your_table
   WHERE ISNULL(Work_Phone, '') <> ''
UNION 
SELECT Cus_ID, 'Home' AS Type, Home_Phone AS ContactNo FROM your_table
   WHERE ISNULL(Home_Phone, '') <> ''
UNION
SELECT Cus_ID, 'Mobile' AS Type, Mobile_Phone AS ContactNo FROM your_table
   WHERE ISNULL(Mobile_Phone, '') <> ''
ORDER BY 1

If there's a chance the "blank" column may contain whitespace characters, then refine it yet further to:

... ISNULL(LTRIM(Work_Phone), '') <> ''

etc.

Answer 2

above result we can achieve using UNPIVOT or Cross Apply also by basing on your assumed data

declare @t table (PK varchar(1),col1 varchar(1),col2 varchar(1),col3 varchar(1))
      insert into @t(PK,col1,col2,col3)values 
        ('X','a','','c'),
        ('y','a','b',''),
        ('z','a','b','c')

Cross Apply :

select PK,value
from @t
cross apply
(
    values
        ('I1', col1),
        ('I2', col2),
        ('I3', col3)
) c(col, value)
where value is not null AND value <> ''
order by PK, col

UNPIVOT

select PK,value
from @t
unpivot
(
  value
  for col in (col1, col2, col3)
) un
WHERE value <> ''
order by PK, col;