Return column in separate row in SELECT statement

Question

In my Customers table, I have PrimaryPhone and SecondaryPhone columns and I'm asked to return them in two separate rows. I also want to mention that in most cases, SecondaryPhone may not even have a value and in those cases, there's no need to return the second row.

So, here's the data coming from Customers table:

CustomerId  |  CompanyName      |  PrimaryPhone    |  SecondaryPhone
123            Acme, Inc.          307-555-1234       307-555-2345
234            DataCorp, Inc.   |  824-555-6547

My SELECT statement should return the following data:

CustomerId  | CompanyName     | Phone
123           Acme, Inc.        307-555-1234
123           Acme, Inc.        307-555-2345
234           DataCorp, Inc.    824-555-6547

I'd appreciate some pointers on how to show data from a column as a separate row. Thanks.

Answer 1

CROSS APPLY would be a good fit here

Select CustomerId
      ,CompanyName
      ,B.*
 From Customers A
 Cross Apply (values (PrimaryPhone)
                    ,(SecondaryPhone)
             ) B(Phone)
  Where Phone is not null

-- EDIT forgot the WHERE

Answer 2

You could try an union like this

SELECT
    CustomerId,
    CompanyName,
    PrimaryPhone as Phone,
FROM dbo.Customers
UNION 
SELECT
    CustomerId,
    CompanyName,
    SecondaryPhone as Phone,
FROM dbo.Customers
WHERE 
    SecondaryPhone IS NOT NULL