INTRODUCTION

There are times you want to get comma (or another character) delimited values from a given field in a SQL table. There are several ways of achieving this goal but I am going to show you a simple manner I usually employ to obtain the desired format.

First of all, we need to have a basic knowledge about how FOR XML clauses work in SQL. You can get more information about this clause from Microsoft in the following link:

https://docs.microsoft.com/en-us/sql/relational-databases/xml/for-xml-sql-server

I am going to use the PATH mode. In a nutshell, the PATH mode let us create simple customized queries mixing attributes and elements. Thus, we can combine them to obtain the specific output format that we need. Detailed examples about its use can be obtained in the next link:

https://docs.microsoft.com/en-us/sql/relational-databases/xml/use-path-mode-with-for-xml

Let's get started by showing a straightforward handy example. Let's consider that we have a very simple table to store data about "Book" entities. This entity will just hold information about the book name, anything else. This is only to make the example very easy to understand. It is evident that in real world "Book" entity should be much more complex. Then, here is the way we are going to create and populate the "Book" table:

declare @Book table (Id int, [Name] nvarchar(200) primary key (Id))
insert into @Book(Id, [Name])
values (1,'Book 1'), (2, 'Book 2'), (3, 'Book 3')

Having said this, let's suppose that our goal is to obtain a comma delimited string with all the book names. It should look like as follows:

Book 1, Book 2, Book 3

We are going to build different queries to firstly show how FOR XML PATH works and secondly to display how we may achieve our final targeted goal.

QUERY 1

Simple query with XML PATH:

declare @Books table (Id int, [Name] nvarchar(200) primary key (Id))
insert into @Books(Id, [Name])
values (1,'Book 1'), (2, 'Book 2'), (3, 'Book 3')
select Id, [Name] from @Books for XML PATH

Results:

As you can see above, all the books are returned inside a "row" element per SQL table row. Within the "row" element, each query field is converted into an element with the same name as it is set up in the query.

QUERY 2

Query with XML Path setting a non empty "row element name" (Book):

declare @Books table (Id int, [Name] nvarchar(200) primary key (Id))
insert into @Books(Id, [Name])
values (1,'Book 1'), (2, 'Book 2'), (3, 'Book 3')
select Id, [Name] from @Books FOR XML PATH('Book')

Results:

<Book>
  <Id>1</Id>
  <Name>Book 1</Name>
</Book>
<Book>
  <Id>2</Id>
  <Name>Book 2</Name>
</Book>
<Book>
  <Id>3</Id>
  <Name>Book 3</Name>
</Book>

We have added a name to the "row element" path in the FOR XML PATH. This means that the element with name "row" in the previous example will be substituted by our "Book" name.

QUERY 3

Query with XML Path setting an empty "row element name":

declare @Books table (Id int, [Name] nvarchar(200) primary key (Id))
insert into @Books(Id, [Name])
values (1,'Book 1'), (2, 'Book 2'), (3, 'Book 3')
select Id, [Name] from @Books for XML PATH('')

Results:

If we assign an empty value to the "row element name" path, there will not be any parent element encapsulating row fields returned by the query.

QUERY 4

Query with XML Path setting an empty "row element name" and just returning "Name" field:

declare @Books table (Id int, [Name] nvarchar(200) primary key (Id))
insert into @Books(Id, [Name])
values (1,'Book 1'), (2, 'Book 2'), (3, 'Book 3')
select [Name] from @Books for XML PATH('')

Results:

Easy to understand, we have removed the "Id" field from the query.

QUERY 5

Query with XML Path setting an empty "row element name", just returning "Name" field and concatenating a "comma" to the field "Name":

declare @Books table (Id int, [Name] nvarchar(200) primary key (Id))
insert into @Books(Id, [Name])
values (1,'Book 1'), (2, 'Book 2'), (3, 'Book 3')
select [Name] + ', ' as NameWithComma from @Books for XML PATH('')

Results:

QUERY 6

Query with XML Path setting an empty "row element name", just returning "Name" field, concatenating a "comma" to the "Name" field and NOT setting any name to this calculated field:

declare @Books table (Id int, [Name] nvarchar(200) primary key (Id))
insert into @Books(Id, [Name])
values (1,'Book 1'), (2, 'Book 2'), (3, 'Book 3')
select [Name] + ', ' from @Books for XML PATH('')

Results:

Book 1, Book 2, Book 3,

Well, We have almost achieved our objective. Notice that we have removed the name of the field returned from the query.

Only it is pending to remove the last "comma" and "space".

Let's update the query in the next example to get the expected result.

QUERY 7

Same as Query 6 but removing unnecessary characters with SUBSTRING SQL function

declare @Books table (Id int, [Name] nvarchar(200) primary key (Id))
insert into @Books(Id, [Name])
values (1,'Book 1'), (2, 'Book 2'), (3, 'Book 3')

declare @Result nvarchar(max)
set @Result = (select [Name] + ', ' from @Books for XML PATH(''))
select substring(@Result,0,len(@Result)-2) as Result

Results:

Book 1, Book 2, Book 3

This case, I am using extra code to remove the two last characters. Notice that we are employing a comma and a space to separate values. This is the reason to employ the SUBSTRING SQL function to trim the result by getting rid of the two last characters.

QUERY 8

Similar to Query 6 but placing "comma" before field and removing unnecessary characters with STUFF SQL function

STUFF SQL function let us insert a string into another string. Besides, we can specify the initial position and the length of characters to be replaced by the new string. The syntax would look like the following:

STUFF(original_string, original_string_initial_position, original_string_length_to_be_removed, new_string_to_be_inserted)

declare @Books table (Id int, [Name] nvarchar(200) primary key (Id))
insert into @Books(Id, [Name])
values (1,'Book 1'), (2, 'Book 2'), (3, 'Book 3')

select (stuff((select ', ' + [Name] from @Books for XML PATH('')), 1 , 2 , '')) as Result

Results:

Book 1, Book 2, Book 3

Note that STUFF SQL function is changing "2" characters from position "1" from the original query by an empty string. In other words, the string ", Book1, Book 2, Book 3" is converted into "Book1, Book 2, Book 3". You can find more information about the STUFF SQL function at here. From my view this is a little neater approach to the previos one.

QUERY 9

Dealing with Special Characters

But, wait, what about dealing with special characters such as "&" or "<"? Is our code working fine or does it have any issue? Let's see.

Let's suppose that we add to the book collection a new one called "Jekyll & Hyde". Note the presence of "&" special character. Let's add this one and execute previous query to obtain results:

declare @Books table (Id int, [Name] nvarchar(200) primary key (Id))
insert into @Books(Id, [Name])
values (1,'Book 1'), (2, 'Book 2'), (3, 'Book 3'), 
	   (4, 'Jekyll & Hyde')

select (stuff((select ', ' + [Name] from @Books for XML PATH('')), 1 , 2 , '')) as Result

Here are the results:

Book 1, Book 2, Book 3, Jekyll &amp; Hyde

You may already notice. Not fun. We are getting "Jekyll &amp; Hyde" instead of "Jekyll & Hyde". Is there any solution? Yes, there is. Let's see the fix in the following query.

QUERY 10

Dealing with Special Characters by using "root" directive and XML type

Let's see first the final SQL query this time:

declare @Books table (Id int, [Name] nvarchar(200) primary key (Id))
insert into @Books(Id, [Name])
values (1,'Book 1'), (2, 'Book 2'), (3, 'Book 3'), 
	   (4, 'Jekyll & Hyde')

select stuff((select ', ' + [Name] from @Books for XML PATH(''), 
				root('books'), 
				type
			).value('/books[1]','nvarchar(max)'), 1, 2, '') as result;

And then let's check the results:

Book 1, Book 2, Book 3, Jekyll & Hyde

As you can see, we got it. The fix works fine. No issues with special characters. Let's explain the process a little more.

To begin with, I have use the "root" directive to encapsulate the query results in a "root" node. I have used "books" as root node name. This way, all the results may be considered as a well-formed XML document. Secondly, I have employed the "type" directive to consider the result as an "xml" SQL type variable. At this stage, the intermediate result would look like this:

<books>, Book 1, Book 2, Book 3, Jekyll &amp; Hyde</books>

As we are managing the previous values as an "xml" sql type, this let me use methods provided by the type in order to execute "xqueries" on it. So, I just have to make use of "/books[1]" xquery to obtain the first (and unique) value of the root node ("books") and convert it into a "nvarchar(max)" type. As you can check, this implies the automatic decoding of special characters. Cool !!

ANOTHER APPROACH

Using COALESCE SQL function along with SELECT clause + VARIABLE

I do not want to finish this little article without showing some other ways of getting the same final result. Perhaps, another very simple way would be something like this:

declare @Books table (Id int, [Name] nvarchar(200) primary key (Id))
insert into @Books(Id, [Name])
values (1,'Book 1'), (2, 'Book 2'), (3, 'Book 3')

DECLARE @Result VARCHAR(MAX)
SELECT @Result = COALESCE(@Result + ', ' ,'') + Name
FROM @Books
SELECT @Result as Result
GO

The SQL COALESCE function takes a list of input parameters and returns the first one non null. Therefore, first iteration throught the query will process the first row from the "@Books" table with a "null" value from "@Result" value. Next iteration will process the second row, but in this case "@Result" variable will hold a value different from "null". Thus, a "comma" will be added to the "@Result" variable and just after the value of "Name" field for the second row. For next rows the behaviour will be the same. Notice that with this approach it is not required to trim the "@Result" variable.

Results:

Book 1, Book 2, Book 3

What could we do as from here? Well, it is up to you but in any case, do not use cursors for solving this issue.

One more thing, do you know any other way of achieving this? Please, add a comment with your proposal. I am looking forward to seeing it 

OTHER RELATED ARTICLES

SQL SERVER – Create a Comma Delimited List Using SELECT Clause From Table Column

Creating a comma delimited list of column values without a SQL cursor at SQLBook.com

I hope this is useful for you.