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