Simple Way to Get Comma Delimited Values from a Field by Using SQL

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.

Background

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 lets 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 link given below:

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

Using the Code

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:

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

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

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 "row element" 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:

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

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

Query 4: Query with XML Path setting an empty "row element" name, 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:

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

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:

<NameWithComma>Book 1, </NameWithComma>
<NameWithComma>Book 2, </NameWithComma>
<NameWithComma>Book 3, </NameWithComma>

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

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, 

This time, query is modified to include the character employed to delimitate values from 'Name' field.

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

In 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

SQL STUFF function lets 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 here. In my view, this is a little neater approach to the previous 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 the 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 & Hyde

You may already notice. Not fun. We are getting "Jekyll & 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 introduce first the final 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 see 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 to 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, 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!

Points of Interest

There are other ways of achieving this goal but please, do not use cursors as a workaround. It is not a good idea to boost your query performance.

In addition to this, I have employed a "comma" to separate values, but you may use another one. It's up to you to modify the queries as you most like or best fit your requirements.

I hope this is useful for you.