A frequent task in reporting is to combine rows together into a comma delimited string. While this can be done in a loop or a cursor, there are other ways to perform this task. If you are only dealing with a single result, there is a quick way to accomplish this with some shorthand:

The output in this case is “a,b,c”, combining the data from the 3 rows into one comma delimited string. Instead of writing the code to loop through the rows and append to the string, we simply have to remove the initial comma before returning the results. This example shows 2 ways to do that, using SUBSTRING() and using STUFF(). While this is a nice shortcut, this doesn’t handle all of the cases that we will be presented with over time. Specifically, we will again need to employ a loop if we need to group these strings in some manner. As it turns out, there is a method by which you can do this without the loop – use XML and STUFF().

To start, lets perform the same task as above, this time using XML and STUFF().

Our output in this case is the same: “a,b,c”. Breaking this down, we can see that we are selecting all of the rows from the temp table. The next step is to append a comma to the beginning of each piece of data. Then, take this data and roll it up into XML, which will bring the data onto a single row. The role that STUFF() plays here is the same as in the first example – removing the initial comma. We used STUFF() in this case instead of SUBSTRING() because the data is XML. In order to use SUBSTRING(), we would have first had to convert the XML back to VARCHAR.

Now, we can look at an example where we have multiple members that will each have their own comma delimited string. For this example, we will call that our “grouping key”.

What we have done here is to use the XML and STUFF() as an inline function to manipulate the data specific to the row we are returning. As such, the output is not a single value. Rather, it is a table of data with our comma delimited strings broken up into 2 rows by the “grouping key”:

grouping_key

mystring

1

a,b,c

2

d,e

One quick note on using XML in this manner – if your data has an ampersands in it, you will need to do some replacing to resolve those conflicts (changing “&amp;” back to “&” due to the XML conversion).