How to create comma separated values in SQL server?

Comma Separated Values in SQL

Predominantly in reporting, you may gone through a situation where you need to convert a comma separated values into list of rows or to convert a list of rows into single column to display in a report. In this splash reading, you will understand the following:

How to convert comma separated data in a column to multiple rows
How to convert multiple rows into one comma separated values
Using COALESCE function
Using STUFF function

Convert comma separated data in a column to multiple rows

Here we have an Customer table with a list of products bought by each customer,

ID	CUSTOMER	PRODUCTS
1	Stuart	Chain Saw,Circular Saw
2	Michael	Drill,Hammer
3	Jonathan	Sticky Notes,Mouse
4	Nabeel	Mobile,Headset

Suppose you want to return this as a single table, list of products bought by each customer. we need to create a function that splits our comma separated columns into rows like you can see below,

CREATE FUNCTION [dbo].[SplitString]
(
@Input NVARCHAR(MAX),
@Character CHAR(1)
)
RETURNS @Output TABLE (
Item NVARCHAR(1000)
)
AS
BEGIN
DECLARE @StartIndex INT, @EndIndex INT

SET @StartIndex = 1
IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
BEGIN
SET @Input = @Input + @Character
END

WHILE CHARINDEX(@Character, @Input) > 0
BEGIN
SET @EndIndex = CHARINDEX(@Character, @Input)

INSERT INTO @Output(Item)
SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
END

RETURN
END

Now, we have created our function to split our columns we need to provide two inputs one is comma separated column values and other one is our separate string comma. This function identifies the comma using CHARINDEX and splits the values using SUBSTRING and inputs into a new row as an Item that will be returned in our select statement. Lets put this function into action,

How to use this function in a select statement?

select c.Id,c.CUSTOMER,o.Item as Product
from CUSTOMERS c
cross apply (select Item from SplitString(c.PRODUCTS,',') ) o

The above statement, retrieves the products based on customer using our SplitString function, the cross apply function used to join a table to a table-valued function is evoked for each row returned from the table. Lets see how the outputs will look like?

The T-SQL now has STRING_SPLIT() function that makes this type of operation very easy. This function was first available in SQL Server 2016. There is no need to create our customized function like we created before we can simply replace that SplitString into STRING_SPLIT this might work it out. But this STRING_SPLIT has a compatibility level that you can see here in the Microsoft documentation. The syntax for using STRING_SPLIT() function is,

SELECT Id,CUSTOMER,value as Product
FROM CUSTOMERS
CROSS APPLY STRING_SPLIT(PRODUCTS, ',');

The returned output will look like this,

Convert multiple rows into one comma-separated values

To convert multiple rows into one comma-separated values, we can use two methods one is using COALESCE function and the other one is using the STUFF function. Suppose if we have products table like below,

Using COALESCE Function

The COALESCE function in SQL returns first the first non-NULL expression among its arguments.

DECLARE @prdlist VARCHAR(MAX)
SELECT @prdlist = COALESCE(@prdlist+', ' ,'') + Name
FROM Products
SELECT @prdlist

This statement returns the list of rows into a single comma-separated values like you see below,

Using STUFF Function

The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

DECLARE @prdlist VARCHAR(MAX)
SET @prdlist =STUFF
(
(
SELECT DISTINCT ', '+ CAST( P.NAME AS VARCHAR(MAX))
FROM Products P
FOR XMl PATH('')
),1,1,''
)
select @prdlist AS NAME

This statement returned output is as follows,