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

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">