Generate Column Value as CSV T-SQL 2000

Question

Hey, Lets say we have a query which generates list of city names as output.

1.India 2.America

Now if we want to make this data look like a CSV i.e.

India,America

then how we will generate it in MS SQL 2000

In 2005I have done this using XM Path

Answer 1

assuming your query is in a table called Table with a field called Nation:

DECLARE @rval varchar(5000)
SELECT @rval = COALESCE(@rval + ',','') + Nation FROM Table
SELECT @rval

If you have India, and America in it...it will return: India,America