Which SQL Query expression?

0 Dave . · January 21, 2015
I need to create a spread sheet from an old database and I am not sure how to do one part. The spread sheet is to have the Person's name, Notes, address and all phone numbers assigned to them.

The issue is that the type of phone number is displayed as a number and not a name.

This is an MS SQL server

Database name Contacts

Tables with relevant data: Person, PersonAddress, Address, PersonContactMethod, ContactMethod and TelephoneNumber.

This code gives me the data I want but I would like ContactMethodId to display differently.

USE Contacts;
SELECT
Person.PersonId,
LastName,
FirstName,
MiddleName,
Note,
PersonContactMethod.ContactMethodTypeId, -- Field to display as Description
--ContactMethod.TelephoneNumberId,
Extension,
ExternalNumber,
--[Address].AddressId,
AddressTypeId,
IsPrimary,
Line1,
Line2,
City,
StateId,
Zip
From Person
LEFT JOIN PersonAddress
ON Person.PersonId=PersonAddress.PersonId
LEFT JOIN [Address]
ON PersonAddress.AddressId=[Address].AddressId
LEFT JOIN PersonContactMethod
ON Person.PersonId=PersonContactMethod.PersonId
LEFT JOIN ContactMethod
ON PersonContactMethod.ContactMethodId=ContactMethod.ContactMethodId
LEFT JOIN TelephoneNumber
ON ContactMethod.TelephoneNumberId=TelephoneNumber.TelephoneNumberId
ORDER BY LastName ASC;


ContactMethodId is a value 1 - 58

I would like it to display as a name

The name is in Table ContactMethodType column "Description".

What code can I add to the above to display this data for ContactMethodId?



ContactMethodTypeIdDescription
1Home Email Address
2Home Phone Number
3Other Email Address
4Other Email Pager
5Other Work Phone Number
6Personal Homepage
7Phone Pager
8Primary Extension
9Text Messaging
10Work Email Address


Thanks for any help












Post a Reply

Replies

Oldest  Newest  Rating
+2 Dave . · January 24, 2015
This is the icing on the cake 

It creates a new column (same name I need to do the import) and places the appropriate value in it.


StationNumber =  CASE PersonContactMethod.ContactMethodTypeId
WHEN 8 THEN Extension
ELSE NULL
END,
StationNumber =  CASE PersonContactMethod.ContactMethodTypeId
WHEN 14 THEN Extension
ELSE NULL
END,
StationNumber = CASE PersonContactMethod.ContactMethodTypeId
WHEN 43 THEN Extension
ELSE NULL
END,
WirelessNumber = CASE PersonContactMethod.ContactMethodTypeId
WHEN 53 THEN Extension
ELSE NULL
END,
MobileNumber = CASE PersonContactMethod.ContactMethodTypeId
WHEN 45 THEN E164Number
ELSE NULL
END,
HomeNumber = CASE PersonContactMethod.ContactMethodTypeId
WHEN 2 THEN E164Number
ELSE NULL
END,
ExternalFax = CASE PersonContactMethod.ContactMethodTypeId
WHEN 54 THEN E164Number
ELSE NULL
END,
ExternalPrimary = CASE PersonContactMethod.ContactMethodTypeId
WHEN 50 THEN E164Number
ELSE NULL
END,
ExternalSecondary = CASE PersonContactMethod.ContactMethodTypeId
WHEN 51 THEN E164Number
ELSE NULL
END,
AlphaPager = CASE PersonContactMethod.ContactMethodTypeId
WHEN 46 THEN E164Number
ELSE NULL
END,
+1 Dave . · January 24, 2015
I found it

By using the CASE function I was able to add the description into the list.


PersonContactMethod.ContactMethodTypeId,
Description = CASE PersonContactMethod.ContactMethodTypeId
WHEN 1 THEN 'Home Email Address'
WHEN 2 THEN 'Home Phone Number'
WHEN 3 THEN 'Other Email Address'
WHEN 4 THEN 'Other Email Pager'
WHEN 5 THEN 'Other Work Phone Number'
WHEN 6 THEN 'Personal Homepage'
WHEN 7 THEN 'Phone Pager'
WHEN 8 THEN 'Primary Extension'
WHEN 9 THEN 'Text Messaging'
WHEN 10 THEN 'Work Email Address'
WHEN 11 THEN 'Work Email Pager'
WHEN 14 THEN 'Secondary Extension'
WHEN 43 THEN 'Tertiary Extension'
WHEN 44 THEN 'Fax'
WHEN 45 THEN 'Mobile Phone'
WHEN 46 THEN 'Alpha Pager'
WHEN 50 THEN 'External Primary Number'
WHEN 51 THEN 'External Secondary Number'
WHEN 52 THEN 'External Tertiary Number'
WHEN 53 THEN 'Wireless'
WHEN 54 THEN 'External Fax'
WHEN 55 THEN 'Assured Mobility Cell'
WHEN 56 THEN 'Assured Mobility WiFi'
WHEN 57 THEN 'Voice Mail'
WHEN 58 THEN 'Instant Messaging'
ELSE 'Not here now'
END,

This helps but I think I can refine the statement more to make the output closer to what I need to input into the new server.
0 Nikola Novakovic · January 22, 2015
You should be able to do something like


PersonContactMethod.ContactMethodTypeId AS Description


THis should alias the column you want. Basically it will rename the colon when its showed somewhere in the output.

Here is the link to the more info:
https://technet.microsoft.com/en-us/library/ms187455(v=sql.105).aspx
  • 1

SQL & Databases

107,120 followers
About

Everything SQL and Databases related in here!

Links
Moderators
Bucky Roberts Administrator