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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.