Pete On Software

RSS Feed

Archive for December, 2009

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.

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()):

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:

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.