T-SQL Comma Delimited Output

One of the common tasks performed when retrieving data from a SQL Server database is returning the result as a comma-delimited output instead of a result set.  This task can be performed by using a CURSOR selecting the column to be concatenated together.  Each row returned by the CURSOR is then concatenated together into a variable separating each one by a comma.

Here’s how the script will look like using the [dbo].[Customers] table in the Northwind database.

DECLARE cCustomerIDs CURSOR FOR
SELECT [CustomerID] FROM [dbo].[Customers] ORDER BY [CustomerID]
DECLARE @CustomerIDs VARCHAR(8000)
DECLARE @CustomerID VARCHAR(10)

OPEN cCustomerIDs
FETCH NEXT FROM cCustomerIDs INTO @CustomerID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CustomerIDs = ISNULL(@CustomerIDs + ',', '') + @CustomerID
FETCH NEXT FROM cCustomerIDs INTO @CustomerID
END

CLOSE cCustomerIDs
DEALLOCATE cCustomerIDs

SELECT @CustomerIDs AS CustomerIDs
GO

A sample output of this script is as follows, using just the first 10 Customer IDs from the Customers table.

CustomerIDs
-----------------------------------------------------------
ALFKI,ANATR,ANTON,AROUT,BERGS,BLAUS,BLONP,BOLID,BONAP,BOTTM

A Simpler Way

Here’s a better and simpler way of doing the same task but without using cursors.

DECLARE @CustomerIDs VARCHAR(8000)

SELECT @CustomerIDs = ISNULL(@CustomerIDs + ',', '') + [CustomerID]
FROM [dbo].[Customers]
ORDER BY [CustomerID]

SELECT @CustomerIDs AS CustomerIDs
GO

Sample Usage

Assuming that you have a table called [dbo].[Health Conditions] with the following data in it:

ID          Name                           ParentID    
----------- ------------------------------ ----------- 
1           Arthritis                      NULL
2           Brain and Nervous System       NULL
3           Cancer                         NULL
4           Fibromyalgia                   1
5           Gout                           1
6           Lupus                          1
7           Osteoarthritis                 1
8           Alzheimer's                    2
9           Epilepsy                       2
10          Multiple Sclerosis             2
11          Parkinson's                    2
12          Breast Cancer                  3
13          Lung Asbestos Cancer           3
14          Melanoma                       3
15          Prostrate Cancer               3

Instead of simply returning the data in a sequential manner as displayed above, you want to retrieve it in the following format:

Name                      Health Conditions                                            
------------------------- ------------------------------------------------------------ 
Arthritis                 Fibromyalgia, Gout, Lupus, Osteoarthritis
Brain and Nervous System  Alzheimer's, Epilepsy, Multiple Sclerosis, Parkinson's
Cancer                    Breast Cancer, Lung Asbestos Cancer, Melanoma, Prostrate Cancer

To accomplish this in a single SELECT statement, a user-defined function must first be created that will return all the health conditions available for a given ID.  Implementing the method of creating a comma-delimited output without using a cursor discussed above, the user-defined function will look as follows:

CREATE FUNCTION [dbo].[ufn_GetHealthConditions] ( @ID INT )
RETURNS VARCHAR(8000)
AS
BEGIN

DECLARE @HealthConditions            VARCHAR(8000)

SELECT @HealthConditions = ISNULL(@HealthConditions + ', ', '') + [Name]
FROM [dbo].[Health Conditions]
WHERE [ParentID] = @ID

RETURN @HealthConditions

END
GO

To return the data from the [dbo].[Health Conditions] table in the given format, the SELECT statement to use is as follows:

SELECT [Name], [dbo].[ufn_GetHealthConditions] ( [ID] )
FROM [dbo].[Health Conditions]
WHERE [ParentID] IS NULL