How to concatenate rows into comma seperated string

I use microsoft SQL server 2000
I have a table of person names and I have the follwing SQL

SELECT FirstName FROM Names

This gives me one column with a row for each person.
Now I want the query to return only one row containing a comma seperated string of all the firstnames.

Is this possible?
Who is Participating?
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
     declare @res varchar(8000)
      select @res = coalesce(@res + ',', '') + FirstName
      from Names
        seelct @res

Kumawat_ShobhaConnect With a Mentor Commented:
try in this way
declare      @str      varchar(60)

select      @str = ''

select      @str      = FirstName + ', ' + @str
from      Names

select      left(@str,len(@str)-1)
you can do it using the for xml clause
i don't know the exact syntax but i know it can be done
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

liversenAuthor Commented:

That does it...more or less
the problem is that the table contains many names (4000) and I have tried to set the  @str to varchar(8000) which seems to be the largest size for a varchar. Still it can not hold all names and the string gets truncated.
Is there another datatype I can use instead or will I have do a loop and get a parts of the names ?

User Varchar(max) if you are working on sql server 2005..
if no then let me know i'll work for other solution as well
liversenAuthor Commented:
It's SQL 2000  :o(
you could try to create a table with a text / image data type and insert the result of the xml query into that column
you can search the web on how to insert the result of for xml query into a table
it is done using the OPENQUERY clause
liversenAuthor Commented:
I have split the points 200 to Kumawat_Shobha because that gave me a working example 300 to aneeshattingal because the solution is more elegant.
The way I have solved the problem with varchar max size of 8000 is to split my query into multiple where i take 500 names at a time.
Thanks everybody
All Courses

From novice to tech pro — start learning today.