{"id":125,"date":"2009-12-11T21:49:30","date_gmt":"2009-12-12T01:49:30","guid":{"rendered":"http:\/\/www.peteonsoftware.com\/?p=125"},"modified":"2024-03-02T16:37:21","modified_gmt":"2024-03-02T21:37:21","slug":"creating-a-comma-separated-list-from-a-sql-grouping","status":"publish","type":"post","link":"https:\/\/www.peteonsoftware.com\/index.php\/2009\/12\/11\/creating-a-comma-separated-list-from-a-sql-grouping\/","title":{"rendered":"Creating a Comma Separated List From a Sql Grouping"},"content":{"rendered":"

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.<\/p>\n

Here is the standard query to return the list of employees and their sales territories from the Northwind database.<\/p>\n

\r\nUSE Northwind\r\ngo\r\n\r\nSELECT e.EmployeeId, e.FirstName, e.LastName, t.TerritoryDescription\r\nFROM Employees e\r\nINNER JOIN EmployeeTerritories et ON e.EmployeeID = et.EmployeeID\r\nINNER JOIN Territories t ON et.TerritoryID = t.TerritoryID\r\n<\/pre>\n
The resulting set looks like the following:
\n\"Result<\/p>\n
What might be best is some kind of built in function that did the following (kind of like C#’s string.Join()):<\/p>\n
\r\nUSE Northwind\r\ngo\r\n\r\n-- This Code is fictitious, CONCATENATE() is not a real aggregate function\r\nSELECT e.EmployeeId, e.FirstName, e.LastName, CONCATENATE(t.TerritoryDescription, ',') as TerritoryList\r\nFROM Employees e\r\nINNER JOIN EmployeeTerritories et ON e.EmployeeID = et.EmployeeID\r\nINNER JOIN Territories t ON et.TerritoryID = t.TerritoryID\r\nGROUP BY e.EmployeeId, e.FirstName, e.LastName\r\n<\/pre>\n
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.<\/p>\n
Running the following code:<\/p>\n
\r\nUSE Northwind\r\ngo\r\n\r\nSELECT e.EmployeeId, e.FirstName, e.LastName,\r\nSTUFF((SELECT ',' + COALESCE(LTRIM(RTRIM(t.TerritoryDescription)), '') \r\nFROM EmployeeTerritories et\r\nINNER JOIN Territories t on et.TerritoryID = t.TerritoryID\r\nWHERE et.EmployeeID = e.EmployeeId\r\nFOR XML PATH('') ), 1, 1, '') as TerritoryList\r\nFROM Employees e\r\n<\/pre>\n
Gives the following result:
\n\"Result<\/p>\n
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()<\/a> 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.<\/p>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[15,87],"tags":[89,4],"class_list":["post-125","post","type-post","status-publish","format-standard","hentry","category-code-tips","category-sql","tag-code-tips","tag-sql"],"_links":{"self":[{"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/posts\/125","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/comments?post=125"}],"version-history":[{"count":0,"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/posts\/125\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/media?parent=125"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/categories?post=125"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/tags?post=125"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}