Pete On Software

RSS Feed

Creating a Comma Separated List From a Sql Grouping

A few times recently when I’ve been creating a view for full text indexing on Sql Server, I’ve had need to return the results of a one to many relationship in one row. I didn’t need dynamic columns or anything, what would suit me best would be to have all of the values just in one comma separated list. After searching the net, I found an ingenious use of FOR XML PATH to solve this problem.

Here is the standard query to return the list of employees and their sales territories from the Northwind database.

USE Northwind

SELECT e.EmployeeId, e.FirstName, e.LastName, t.TerritoryDescription
FROM Employees e
INNER JOIN EmployeeTerritories et ON e.EmployeeID = et.EmployeeID
INNER JOIN Territories t ON et.TerritoryID = t.TerritoryID

The resulting set looks like the following:
Result set with no grouping or concatenation.

What might be best is some kind of built in function that did the following (kind of like C#’s string.Join()):

USE Northwind

-- This Code is fictitious, CONCATENATE() is not a real aggregate function
SELECT e.EmployeeId, e.FirstName, e.LastName, CONCATENATE(t.TerritoryDescription, ',') as TerritoryList
FROM Employees e
INNER JOIN EmployeeTerritories et ON e.EmployeeID = et.EmployeeID
INNER JOIN Territories t ON et.TerritoryID = t.TerritoryID
GROUP BY e.EmployeeId, e.FirstName, e.LastName

However, nothing like that was to be found. What I did find was someone who used FOR XML PATH in an interesting way. Normally, FOR XML PATH would return some normal looking xml, however if you pass in the ” as the argument, you just end up with all of the values that would be in an xml string, but with no resulting tag soup.

Running the following code:

USE Northwind

SELECT e.EmployeeId, e.FirstName, e.LastName,
STUFF((SELECT ',' + COALESCE(LTRIM(RTRIM(t.TerritoryDescription)), '') 
FROM EmployeeTerritories et
INNER JOIN Territories t on et.TerritoryID = t.TerritoryID
WHERE et.EmployeeID = e.EmployeeId
FOR XML PATH('') ), 1, 1, '') as TerritoryList
FROM Employees e

Gives the following result:
Result set with grouping and concatenation.

This does do a subselect, but I’ve found that it doesn’t hurt performance too badly. The only other trick here is the STUFF() function, which replaces part of a string with another. In this case, I’m replacing only the first character (the 1, 1) arguments with just an empty string, effectively removing our extra comma.

I hope that someone may have found this useful and if you have any other techniques for solving this kind of problem, I’d love to hear them.


Kuldeep singh  on March 16th, 2016

Nice query. Thanks buddy.
Great work.

Imran  on December 22nd, 2016

Thanks. Great Stuff. The other articles were showing examples of creating a list of columns and grouping from the same table. Yours was a clear example of three tables where there are two master tables with an integration table.

Thanks again.

1developer  on July 18th, 2017

Sql server 2017 has string aggregate function STRING_AGG()

Pete  on July 19th, 2017


That is fantastic! Finally! Thanks for telling me about that, I hadn’t seen that yet, and now I’m pretty excited for it.

rondc  on July 21st, 2021

Hi Pete, thanks great query!. From your example, how would I be able to generate this in reverse. I have a table in the form of your result set needing to generate a dataset in the form of the Employees table, and I am stuck.
Thanks again.

Pete  on July 21st, 2021


Is is really easy. Basically, you just need to use the STRING_SPLIT() function and a CROSS APPLY

CREATE TABLE #EmployeeTerritory (EmployeeId INT, FirstName VARCHAR(50), LastName VARCHAR(50), TerritoryList VARCHAR(200));

INSERT #EmployeeTerritory SELECT 1, 'Nancy', 'Davolio', 'Wilton,Neward';
INSERT #EmployeeTerritory SELECT 2, 'Andrew', 'Fuller', 'Westboro,Bedford,Georgetow,Boston,Cambridge,Braintree,Louisville';
INSERT #EmployeeTerritory SELECT 3, 'Janet', 'Leverling', 'Atlanta,Savannah,Orlando,Tampa';

SELECT et.EmployeeId, et.FirstName, et.LastName, tl.value AS Territory
FROM #EmployeeTerritory et
CROSS APPLY STRING_SPLIT(TerritoryList, ',') tl

That code gives you the results shown here.

Leave a Comment