Month: December 2009

Code Tips

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
go

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
go

-- 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
go

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.