column values into one string in sql stored procedure

Question

I have and address table with the following columns:

• addressId
• buildingName
• streetNo
• streetName
• streetType
• subAddressNo
• subAddressType
• suburb
• postCode
• state

What I want is to SELECT * from address where addressId = @addressId, and that return would be all columns concatenated into one string. Something like:

set @addressString = (SELECT * from address where addressId = @addressId)

Answer 1

select coalesce(cast(addressId as varchar)+ ',', '')  + coalesce(buildingName+ ',', '') 
coalesce(cast(streetNo as varchar)+ ',', '') -- + and so on 
from address

remember to cast the numeric types as varchar

Answer 2

Try this:

SELECT buldingName + ' ' + streetNo + ' ' + streetName + ' ' + theRestOfYourColumns AS ClientAddress
FROM YourAddressTable
WHERE addressId = @addressIdPreviouslyAssignedVariable