(Print this page)

SQL - Concatenate values multiple from multiple lines
Published date: Friday, October 30, 2009
On: Moer and Éric Moreau's web site

Very often, I need to concatenate values from multiple lines. For a couple of years now, I have been using user-defined functions to achieve that. But I just found a better solution to that problem.

For example, in the good old Northwind database, we have a Region table containing 2 columns (RegionID and RegionDescription). We also have a Territories table containing 3 columns (TerritoryID, TerritoryDescription, and RegionID). This last column (RegionID) can be used to join both tables together. But if you would like to have all the territories description for a region into a single column, then my trick comes in handy.

Consider this T-SQL query:

SELECT RegionID, RegionDescription 
, STUFF(
       (SELECT ', ' + TerritoryDescription
        FROM Territories
        WHERE RegionID = R.RegionID
        ORDER BY TerritoryDescription
        FOR XML PATH('')
       )
       , 1
       , 2
       , '') AS Territories FROM Region AS R

This simple query would return exactly what I just described:

RegionID RegionDescription Territories
1 Eastern Bedford, Boston, Braintree, Cambridge, Cary, Edison, Fairport, Georgetow, Greensboro, Louisville, Mellvile, Morristown, New York, New York, Neward, Providence, Rockville, Westboro, Wilton
2 Western Bellevue, Campbell, Chicago, Colorado Springs, Denver, Hoffman Estates, Menlo Park, Phoenix, Redmond, San Francisco, Santa Clara, Santa Cruz, Santa Monica, Scottsdale, Seattle
3 Northern Beachwood, Bloomfield Hills, Findlay, Hollis, Minneapolis, Philadelphia, Portsmouth, Racine, Roseville, Southfield, Troy
4 Southern Atlanta, Austin, Bentonville, Columbia, Dallas, Orlando, Savannah, Tampa

The magic lies into the “For XML Path(‘’)” part. We normally use this syntax to create an XML string with some value as tags but if we do not provide this value (as I do), no tags are added thus resulting in a plain old string.

The STUFF function is only there to replace the first coma and blank that starts the list of territories. I hope you will find this simple tip helpful.

(Print this page)