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:
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:
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.