SQL SERVER – Creating Comma Separate Values List from Table – UDF – SP

 

Following script will create common separate values (CSV) or common separate list from tables. convert list to table. Following script is written for SQL SERVER 2005. It will also work well with very big TEXT field. 

If you want to use this on SQL SERVER 2000 replace VARCHAR(MAX) with VARCHAR(8000) or any other varchar limit. It will work with INT as well as VARCHAR.

There are three ways to do this. 1) Using COALESCE 2) Using SELECT Smartly 3) Using CURSOR.

The table is example is:

TableName: NumberTable

NumberCols

first

second

third

fourth

fifth

Output : first,second,third,fourth,fifth

Option 1: This is the smartest way.

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' , '') + NumberCols
FROM NumberTable
SELECT @listStr

Please make a note that COALESCE returns the first NOT NULL value from the argument list we pass.

Option 2: This is the smart but not the best way; though I have seen similar code many times.

DECLARE @listStr VARCHAR(MAX)
SET @listStr = ''
SELECT @listStr = @listStr + NumberCols + ','
FROM NumberTable
SELECT SUBSTRING(@listStr , 1, LEN(@listStr)-1)

I sometime use ISNULL(NumberCols,’NullValue’) to convert NULL values to other desired value. 

Option 3: Cursor are not the best way, please use either of above options.

Above script can be converted to User Defined Function (UDF) or Storped Procedure (SP).