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

Posted: August 25, 2009 in SQL
Tags: , ,

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.

Advertisements
Comments
  1. Nitendra says:

    very nice out put what i want

  2. Duncan says:

    I think you’ll find much better performance if you SELECT … FOR XML, then use SUBSTITUTE to replace the elements with “, “. If your list is small you might not notice a difference, but my list has 100,000 elements so going from 30 seconds to <1 is pretty significant.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s