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: One,Two,Three,Four).

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 REPLCACE 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:

See the mark value in output, It will replace “Sawai madhopur” to “Sawai,Madhopur”.

2. Main Limitation of FOR XML PATH is it cannot be used to make the delimiter separated values for more than 1 column. Follow Step 1 instead.

Conclusion

Unfortunately, you can't make it generic (tables and columns are different for each task), but it can be implemented as template.