asked on

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.

SOLUTION

Kumawat_Shobha

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

momi_sabag

you can do it using the for xml clause
i don't know the exact syntax but i know it can be done

liversen

ASKER

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 ?

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kumawat_Shobha

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

liversen

ASKER

It's SQL 2000 :o(

momi_sabag

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

liversen

ASKER

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