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:>/p>

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