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.

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.

4 Comments

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

1developer  on July 18th, 2017

Hi,
Sql server 2017 has string aggregate function STRING_AGG()

Pete  on July 19th, 2017

1developer,

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

Leave a Comment