Munavvar Husein's Glory: Generating Comma separated list through T-SQL

Introduction

Many times while working on LOB applications, we often land into a situation where we want to generate a delimiter separated string from the record set. Here, I will walk through the different ways which I know in which we can achieve the same. For simplicity sake, the delimiter I have chosen here is a comma (‘,’)

Implementation

Let’s first create some sample data.

DECLARE @Cities AS TABLE

(

[CityID] [int] IDENTITY(1,1) NOT NULL,

[CityName] [varchar](100) NULL,

PRIMARY KEY ([CityID])

)

INSERT INTO @Cities

SELECT 'Surat' UNION ALL

SELECT 'Mumbai' UNION ALL

SELECT 'Sawai Madhopur' UNION ALL

SELECT 'Ahmedabad'

We want to create a list of all the valid @Lookup values (Example: Surat,Mumbai,Sawai Madhopur,Ahmedabad).

To get this one would normally use either a Cursor or WHILE loop to read through the table above. Instead there are many easier ways to do the same.

First let's list the traditional CURSOR approach.

--------------- CURSOR approach ---------------

DECLARE @CityNameList VARCHAR(1000)

DECLARE @CityNames VARCHAR(25)

SET @CityNameList = ''

DECLARE clookup CURSOR

FOR SELECT DISTINCT CityName

FROM @Cities

OPEN clookup

FETCH NEXT FROM clookup INTO @CityNames

WHILE @@FETCH_STATUS = 0

BEGIN

SET @CityNameList = ISNULL(@CityNameList,'') + @CityNames + ','

FETCH NEXT FROM clookup INTO @CityNames

END

CLOSE clookup

DEALLOCATE clookup

SET @CityNameList = SUBSTRING(@CityNameList, 1, LEN(@CityNameList)-1) -- drop the trailing delimiter

SELECT @CityNameList

-------------------------------------------------------------------

Now we can see other approaches which are better than the above approach.

----------------------Other Approaches----------------------

1. This way is very known and simplest which is used by most of the programmers.

DECLARE @CitieNames AS VARCHAR(MAX)

DECLARE @CitieIDs AS VARCHAR(MAX)

SELECT

@CitieIDs = COALESCE(@CitieIDs + ',', '') + CONVERT(VARCHAR, C.CityID),

@CitieNames = COALESCE(@CitieNames + ',', '') + C.CityName

FROM

@Cities C

SELECT @CitieIDs CityIDs, @CitieNames Cities

--Output:

2. Now I want to make only comma separated ids.

SELECT

REPLACE

(

SELECT

C.CityID as 'data()'

FROM

@Cities C

FOR XML PATH('')

), ' ', ','

) CityIDs

--Output

3. Now I want to make only comma separated names.

SELECT

STUFF(

(

SELECT

',' + C.CityName

FROM

@Cities C

FOR XML PATH('')

), 1,1,''

) CityNames

--Output:

After experiencing the different approaches, I would like you to concentrate on the below points -

1. Usage of REPLACE in Step 2 and STUFF in Step 3.

Performance wise Replace function gives the better performance. However, it cannot be used always. Suppose, I will make the comma separated names with the use of replace function it will not give the proper output like,

--Output: