Concatenating Addresses

[drbtodd1971]

Hi,
I've got three tables and have written the following code which brings back the data I require. However some people have more than one plot number so I need a way to create a tempoary table with the person details and all their plot details concatenated into one field with a carriage return after each address. Think that is chr(10) and chr(13).

Tables are:

tblPerson - PersonID is key
tblPersonLandInterests - PersonID and Plot_Number as keys
tblLandDetails - Plot_Number is key

my code is as follows:

select distinct PLI.Plot_number, P.PersonID, P.Person_FirstName, P.Person_Surname, P.Person_JobTitle, P.Person_BusinessName,
P.Person_Address1, P.Person_Address2, P.Person_Address3, P.Person_Town, P.Person_County, P.Person_PostCode,
convert(varchar (5),LD.Plot_Number) + ' ' + LD.PrimaryName + ' ' + LD.Street
from tblPersonLandInterests PLI, tblPerson P, tblLandDetails LD
where P.PersonID = PLI.PersonID
and PLI.Plot_Number = LD.Plot_Number

Any help would be much appreciated.

 

[AlexCuse]

Have a look here:

thread183-1159740

Just concatenate your rows with char(10) + char(13) rather than the comma.

HOpe this helps,

ALex

Ignorance of certain subjects is a great part of wisdom