Saturday 9 April 2011

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:

clip_image001

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.

No comments:

Post a Comment