Creating Comma Separate Values List from Table – UDF – SP

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).