SQL: Match a variable value with multiple columns and select matching column name

user_1181942 0
Viewed 311

I want to get the record with matching phone number. There are total 3 columns that saves phone numbers. Work, home and cellphone. I am writing a query to check if the phone number matches with any of the 3 columns.

declare @phNo nvarchar(15) = 'xxxxxxxxxx'

select HomePhone, cellphone, workphone, * 
from Contact_Table 
where @phNo in (HomePhone, cellphone, workphone)

This query is working fine, but I also want the name of the column which matches with the phone number. How can I get the matching column's name?

For example if the phone number matches with 'homehpone' then the result set should return 'homephone'

tsql sql-server sql
2 Answers

This can be done with CASE:

DECLARE @phNo NVARCHAR(15) = 'xxxxxxxxxx';

SELECT homephone,cellphone,workphone, *,
       CASE WHEN @phNo = homephone THEN 'homephone'
            WHEN @phNo = cellphone then 'cellphone'
            WHEN @phNo = workphone THEN 'workphone'
            END AS PhoneMatch
FROM Contact_Table
WHERE @phNo IN (HomePhone,cellphone,workphone);
user_758520

Using an APPLY operator is also an option:

DECLARE @phNo nvarchar(15) = 'xxxxxxxxxx'

SELECT HomePhone, CellPhone, WorkPhone, a.PhoneType 
FROM (VALUES
   ('xxxxxxxxxx', '000-000-000', '000-000-000'),
   ('000-000-000', '000-000-000', '000-000-000'),
   ('000-000-000', 'xxxxxxxxxx', '000-000-000')
) t (HomePhone, CellPhone, WorkPhone)
OUTER APPLY (VALUES
   (t.HomePhone, 'HomePhone'), 
   (t.CellPhone, 'CellPhone'),
   (t.WorkPhone, 'WorkPhone')
) a (Phone, PhoneType) 
WHERE @phNo = a.Phone

Result:

HomePhone CellPhone WorkPhone PhoneType
xxxxxxxxxx 000-000-000 000-000-000 HomePhone
000-000-000 xxxxxxxxxx 000-000-000 CellPhone
user_65780800
Related