Comma Separated Values, or values that are delimited by a specific character, are an easy way to pass a list of values from a function or in a dataset.  There are a couple ways to create comma separated values within SQL Server, as a single set which could be useful in a scalar function, or in a dataset which could allow passing additional values in a flat data set.  This blog post is going to focus on building a scalar delimited list using a NULL value function such as ISNULL and COALESCE.   There is a misconception that COALESCE has a mystical power which is needed to build a delimited list, we will take a look at this mystical power and show that the NULL value functions are technically not needed.

Before we dive into building the delimited lists, a quick review of the NULL value functions will be helpful. Both the ISNULL and COALESCE statements can be used to replace NULL values with a different specified value. However the COALSCE statement can evaluate multiple NULL value expressions checks whereas the ISNULL statement has a single evaluation and replacement. Here are a couple links with additional details regarding the COALSCE and ISNULL functions.

So the idea for this blog post is that we take a list of first and last names and return a concatenated, comma separated list. Here is our sample dataset.

What we would expect the script to return is a comma separated list of values that looks like this:

Using the following temp table structure and sampling of first and last names gives us the data that we will need for this example.

DROP TABLE IF EXISTS #Person
GO
 
CREATE TABLE #Person (
   FirstName NVARCHAR(50)
 , LastName NVARCHAR(50))
GO
 
INSERT   INTO #Person
         (FirstName, LastName)
VALUES   (N'Bob',N'Roberts')
	   , (N'John',N'Johnson')
	   , (N'Tom',N'Tompson')
	   , (N'Rich',N'Richardson')
	   , (N'Will',N'Williams');
GO

The first step is to build out the string that we will put between the delimited values, in this case we will concatenate the first and last names. Instead of using the addition operator, we could have used the CONCAT function.

SELECT  p.FirstName + ' ' + p.LastName
FROM    #Person p;

As you can see from the result set, we have all of the names concatenated. Now we need to build all of the values into a single result. We can take and store all of the values into a variable, then return the result from the variable

DECLARE @FLNameCSV NVARCHAR(500);
 
SELECT  @FLNameCSV = p.FirstName + ' ' + p.LastName
FROM    #Person p;
 
SELECT  @FLNameCSV;

The interesting fact here is that only the last value was persisted in the variable. In truth each of the values that were returned in the result set were written to the variable, but each value was overwritten by the next value. We will use this concept by concatenating the variable value with the first and last name values.

DECLARE @FLNameCSV NVARCHAR(500);
 
SELECT  @FLNameCSV = @FLNameCSV + ', ' + p.FirstName + ' ' + p.LastName
FROM    #Person p;
 
SELECT  @FLNameCSV;

A NULL value was returned??? This is because the variable value was never initialized, therefore was set to NULL. When you concatenate a NULL value with any value you will end up with NULL. This is where the COALESCE function comes into play. If we use the COALSCE function to check for the NULL value and properly handle the NULL, we can properly concatenate our values and store them into the variable

DECLARE @FLNameCSV NVARCHAR(500);
 
SELECT  @FLNameCSV = COALESCE(@FLNameCSV + ', ', '') + p.FirstName + ' ' + p.LastName
FROM    #Person p;
 
SELECT  @FLNameCSV;

So as we can see, the magic is not within the COALSCE function it is actually the variable concatenation that is the magic behind building the list of values. Further proof??? Let’s remove the COALSCE and assign a default value to our variable.

DECLARE @FLNameCSV NVARCHAR(500) = '';
 
SELECT  @FLNameCSV = @FLNameCSV + ', ' + p.FirstName + ' ' + p.LastName
FROM    #Person p;
 
SELECT  @FLNameCSV;

We have our delimited list with an extra comma in the front, we can simply use function like SUBSTRING to remove the first few characters.

DECLARE @FLNameCSV NVARCHAR(500) = '';
 
SELECT  @FLNameCSV = @FLNameCSV + ', ' + p.FirstName + ' ' + p.LastName
FROM    #Person p;
 
SELECT  SUBSTRING(@FLNameCSV, 3, LEN(@FLNameCSV));

There we have it, the truth behind comma separated lists and the COALSCE function. In a future blog post, we will take a look at building comma separated lists using the FOR XML clause.