How to concatenate rows into comma seperated string

Hi
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?
thanks.
LVL 1
liversenAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
     declare @res varchar(8000)
      select @res = coalesce(@res + ',', '') + FirstName
      from Names
        seelct @res

0
 
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)
0
 
momi_sabagCommented:
you can do it using the for xml clause
i don't know the exact syntax but i know it can be done
0
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 ?

0
 
Kumawat_ShobhaCommented:
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
0
 
liversenAuthor Commented:
It's SQL 2000  :o(
0
 
momi_sabagCommented:
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
0
 
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
0
All Courses

From novice to tech pro — start learning today.