Forming a comma Separated String in SQL Server 2000

There are hundreds of T-SQL requirements that lead to innovative and faster coding practices. But the requirement in the industries keep on increasing. Here is one such requirement that I’ve been asked by many peers. Assume you have a table which after some filtering yields the below output.

S.No	Val
1	V
2	I
3	N
4	O
5	D

But our requirement is to get a comma separated value from this resultant table. How can I achieve this. The basic and the normal coding practice of a programmer would be to use a PL-SQL block using Cursor and then form the whole string using a While Loop. Below is a typical code to illustrate the same. I’ve attached the sample script required for the example to work.

Declare @str Varchar(100), @tempVar Varchar(20) -- Initialize the variables Set @str='' -- Declare the Cursor to hold the values Declare my_cur Cursor FOR Select Val from vin_temp Open my_Cur -- Loop the Cursor to get the comma separated values FETCH NEXT FROM my_Cur INTO @tempVar WHILE @@FETCH_STATUS = 0 BEGIN             SET @str=@str + @tempVar + ','             FETCH NEXT FROM my_Cur INTO @tempVar END -- Remove the last comma before showing the output Select SUBSTRING(@str , 1,Len(@str)-1) -- Clear the memory Close my_cur Deallocate my_cur

Now we have found a way to form the comma separated listing. But often using such cursors are costlier and can be performance intensive. Having said this there are alternate ways to form this comma separated string in T-SQL code.

Declare @str Varchar(100) -- Initialize the String Set @str='' -- Form the comma separated string Select @str=@str + Val + ',' from vin_temp -- Remove the trailing comma Select SUBSTRING(@str , 1,Len(@str)-1)

The above method of concatenating the string within a SQL statement is the fastest and less intensive in operation. Now taking a closer look into this statement we can observe that we had to remove the trailing comma at both the instances. Now moving forward we could think if there is even more better strategies to get the same output. So below is an implementation that has refined over the previous example.

Declare @str Varchar(100) -- Forming the comma separated string Select @str= COALESCE(@str+',' , '') + Val from vin_temp Select @str

Now we would look at the above statement and feel how it works. Well SQL Server by default returns NULL when a NULL is concatenated. And we use this principle to get our desired output. Now COALESCE returns the first NOT NULL value from the argument list we pass. Hence during the first run it concatenates NULL(@str) with comma (‘,’) and hence yields NULL giving us an empty string. Later the concatenation works the normal manner.

Script

-- Use this samples scripts Create Table vin_temp (id int, Val Varchar(10)) Insert into vin_temp values(1,'V') Insert into vin_temp values(2,'I') Insert into vin_temp values(3,'N') Insert into vin_temp values(4,'O') Insert into vin_temp values(5,'D') -- For verification of the data Select * from vin_temp -- Used to drop the temp table -- Drop table vin_temp