Concatenate query results in SQL Server using Coalesce

If you want to concatenate the result of a query and display it in a single row, you can use COALESCE.

We will concatenate results of this query:

USE AdventureWorks
SELECT AddressID
FROM Person.Address
WHERE (City = 'Toronto')
ORDER BY AddressID

To concatenate all the AddressIDs from Person.Address table in AdventureWorks Database for all people living in Toronto use this query (AddressID is INTEGER datatype and needs to be converted using CONVERT or CAST to string data type):

USE AdventureWorks
DECLARE @AddressID VARCHAR(2000)
SELECT @AddressID = COALESCE(@AddressID,'') + CONVERT(VARCHAR, AddressID) + ', '
FROM Person.Address
WHERE (City = 'Toronto')
ORDER BY AddressID
SELECT @AddressID AS AddressIDList

To concatenate results of this query:

USE AdventureWorks
SELECT AddressID, AddressLine1
FROM Person.Address
WHERE (City = 'Toronto')
ORDER BY AddressID

Use this code:

USE AdventureWorks
DECLARE @Concatenated VARCHAR(2000)
SELECT @Concatenated = COALESCE(@Concatenated,'') + CONVERT(VARCHAR, AddressID) + ', ' + AddressLine1 + ', '
FROM Person.Address
WHERE (City = 'Toronto')
ORDER BY AddressID
SELECT @Concatenated AS ConcatenatedData