CODEWITHSUNDEEP
sql-servert-sql
Sam
Sam

Reputation: 30388

Return column in separate row in SELECT statement

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.

Upvotes: 7

Views: 927

Answers (3)

THE LIFE-TIME LEARNER
THE LIFE-TIME LEARNER

Reputation: 1522

This is Perfect way(Query) to solve Your Problem

SELECT
    CustomerId,
    CompanyName,
    PrimaryPhone as Phone
FROM dbo.Customer
UNION  
SELECT
    CustomerId,
    CompanyName,
    SecondaryPhone as Phone
FROM dbo.Customer
WHERE 
  DATALENGTH(SecondaryPhone)<>0

Output:

Output

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81960

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

Upvotes: 8

HappyPengins
HappyPengins

Reputation: 121

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

Upvotes: 3

Related Questions