CODEWITHSUNDEEP
sqlsql-server
an1234
an1234

Reputation: 165

Create Phone type column from the source of phone numbers

I have two tables, account and customer: 

Create Table Account
(
      PrimaryRowId varchar(12),
      SecondaryRowId varchar (12),
      AccountNumber varchar(50)
);

Create table Customer
(
    CustomerRowId varchar(12),
    MobilePhone varchar(20),
    HomePhone Varchar(20),
    CellPhone Varchar(20)
);

What I would like to see is an output that shows the account, the phonenumber, and the phonenumbertype. This PhoneNumberType will be based off of where my numbers come from. So if they come from mobilePhone column I would like to have mobile for the phonenumbertype. I already have a way a getting the primary and secondary accounts and their phoneNumbers. I would now like to give them a phonetype, so mobile would get mobile, home would get personal, work would get work and so on. Is there a way I can accomplish this by basing it off of where the number comes from(Mobilephone, HomePhone, cellPhone). So say I have a mobile number 8773409646 and that number comes from mobile. I would like to see the accountnumber, phonenumber, and beside the phoneNumber in another field named phonetype I would like to give it mobile.

Expected Results:

AccountNumber   PhoneNumber   PhoneType
-----------------------------------------
  34567434       8773409646     Mobile

Upvotes: 0

Views: 1894

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270553

SQL Server allows you to unpivot values using APPLY. This is very handy in this case:

SELECT a.AccountNumber, v.PhoneNumber, v.PhoneType
FROM Account a JOIN
     Customer c
     ON c.CustomerRowId IN (a.PrimaryRowId, a.SecondaryRowId) CROSS APPLY
     (VALUES ('Mobile', MobilePhone),
             ('Home', HomePhone),
             ('Cell', CellPhone)
     ) v(PhoneType, PhoneNumber)
WHERE v.PhoneNumber IS NOT NULL;

Upvotes: 0

Mithun ambadi
Mithun ambadi

Reputation: 1

SELECT 
	A.AccountNumber,
	CASE
		WHEN B.MobilePhone <> '' THEN B.MobilePhone 
		WHEN B.HomePhone <> '' THEN B.HomePhone
		WHEN B.CellPhone <> '' THEN B.CellPhone
	END AS PhoneNumber ,
	CASE
		WHEN B.MobilePhone <> '' THEN 'MobilePhone'
		WHEN B.HomePhone <> '' THEN 'HomePhone'
		WHEN B.CellPhone <> '' THEN 'CellPhone'
	END AS PhoneType
FROM Account A
	JOIN Customer B ON
	A.PrimaryRowId = B.CustomerRowId

Upvotes: 0

Nick
Nick

Reputation: 147206

You need to join the Accounts table to a list of all the phone numbers and their types in the Customer table, which you can create with a UNION ALL query:

SELECT a.AccountNumber, c.PhoneNumber, c.PhoneType
FROM Account a
JOIN (SELECT CustomerRowId, MobilePhone AS PhoneNumber, 'Mobile' AS PhoneType
      FROM Customer
      WHERE MobilePhone != ''
      UNION ALL
      SELECT CustomerRowId, HomePhone, 'Home'
      FROM Customer
      WHERE HomePhone != ''
      UNION ALL
      SELECT CustomerRowId, CellPhone, 'Cell'
      FROM Customer
      WHERE CellPhone != '') c ON c.CustomerRowId IN (a.PrimaryRowId, a.SecondaryRowId)

Demo on dbfiddle

Upvotes: 3

Related Questions