How to get comma separated values from a database table without using cursor

You can retrieve comma separated values from a database table using following type of SQL scripts

DECLARE @varList VARCHAR(1000)
SET @varList = SPACE(0)
SELECT @varList = @varList + ', ' + ContactName
FROM Customers
SELECT @varList
SELECT SUBSTRING(@varList, 3, 1000)

You can see that the statement “SELECT @varList” will return list like “ , Maria Anders, Ana Trujillo, Antonio Moreno, Thomas Hardy….etc ”

Here you can see the result will have extra comma at the start of the string result. To remove this I have added another select statement “SELECT SUBSTRING(@varList, 3, 1000)”. This will return string upto 1000 characters only, you can set this value as per your requirements.