SQL Query combine like results

Vote count:

0

I have a database on a server that I need to extract certain data to import into a newer version of the same product. The issue is the old version is so old that the manufacturer does not have any migration path to the new product. The only way to do it is a manual re key or manual export of data from the SQL database to the new import format.

The database is on a MS SQL Server and I am working on a copy in a test environment.

The data I need is basically person contact information PersonId, FirstName, LastName, Address, and contact phone numbers (a couple of other fields as well).

I have written a SQL script to generate the data I need but because a person can have multiple contact numbers it gives me multiple results for the same person.

For example lets say we have two persons in the database Bill NoName and Sally Example.

Bill has only one contact number his extension number so his results show up as a single line. Sally has three contact numbers so my query creates three lines.

PersonID, FirstName, LastName, Address, Extension, HomePhone, Mobile,
1049, Bill, NoName, 123 sample st, Any City, Any State, 3343, null, null
1050, Sally, Example, 124 sample st, city, state, 3344, null, null
1050, Sally, Example, 124 sample st, city, state, null, 5555551212, null
1050, Sally, Example, 124 sample st, city, state, null, null, 5555554545

I would like to reduce it down to one line per PersonId but have been unsuccessful.

1050, Sally, Example, 124 sample st, city, state, 3344, 5555551212, 5555554545

Here is my code to create the report

How can I merge or combine the persons with multiple entries?

USE OW5000;
SELECT
Person.PersonId,
LastName,
FirstName, 
MiddleName, 
Note, 
MessageCenterInstructions,
StationNumber = CASE PersonContactMethod.ContactMethodTypeId
WHEN 8 THEN Extension
ELSE NULL
END,
StationNumber2 = CASE PersonContactMethod.ContactMethodTypeId
WHEN 14 THEN Extension
ELSE NULL
END,
StationNumber3 = 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,
PhonePager = CASE PersonContactMethod.ContactMethodTypeId
WHEN 7 THEN E164Number
ELSE NULL
END,
Fax = CASE PersonContactMethod.ContactMethodTypeId
WHEN 44 THEN E164Number
ELSE NULL
END,
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, FirstName ASC;

asked 41 secs ago

Double Driveline

1

SQL Query combine like results