Need sql to return the result of a query as comma seperated values.

Hi,
I need a sql that returns thequery result as comma seperated list of values, instead of several rows. Below is the scenario...
Table Name - Customer
Columns - CustomerID, Join Date
Say below is the data of Customer table ...
CustomerID JoinDate
1 04/01/2005
2 01/03/2003
3 06/02/2004
4 01/05/2002
5 09/07/2005
Now i want to retrieve all the customerid's who have joined this year. Below is the query that i use for this case.
Select CustomerID from Customer where JoinDate between '01/01/2005' and GetDate()
This gives the below result as two rows.
CustomerID
1
5
But i need to get the result as '1,5' (comma seperated list of resulting values).
Any help is highly appreciated
Thanks in Advance
Ramesh

You need declare a variable :
declare @.value nvarchar(200)
Select@.value=case when @.value is null then '' else @.value+',' end+cast(CustomerID as varchar) from Customer where JoinDate between '01/01/2005' and GetDate()
|||Hi,
You can manage your goal by using COALESCE
Please check the following URLs for sample COALESCE usage for returning the column values of a tables in a string seperated by a delimeter character.
http://www.kodyaz.com/ShowPost.aspx?PostID=76
http://www.kodyaz.com/article.aspx?ArticleID=29

As a sample you can run the below code on Northwind database also
DECLARE @.s as nvarchar(4000)
DECLARE @.char as char(1)
SELECT @.char = ','
SELECT @.s = @.char
SELECT @.s = COALESCE(FirstName + @.char + @.s , '') FROM Employees
SELECT SUBSTRING(@.s, 0, LEN(@.s)-1) AS Employees

I hope this helps,
Eralper
|||

Hi All,

I Solved the problem with the below query...

DECLARE @.CustomerIDs VARCHAR(8000)

SELECT @.CustomerIDs = ISNULL(@.CustomerIDs + ',', '') + CAST(CustomerID AS VARCHAR(10))
FROM CUSTOMER
WHERE JoinDate BETWEEN '01/01/2005' and GetDate()

SELECT @.CustomerIDs AS CustomerID

|||It's great! It really worked fine. Thanks a lot...

Need help: With Dutch regional options, MS SQL Server BCP incorrectly delivers v

Hi all,

With Windows regional options are set to Dutch/Netherlands (or any other country using comma as the decimal symbol), MS SQL Server BCP Delivers incorrect figures to the target table.

The source table is a text file containing commas as the decimal character (As an example, in the text file 100.000 is represented as 100,000).

The target table is a Sql Server database table, with the column in question defined as a FLOAT datatype.

When BCP is run to deliver the data from the text file to Sql Server,
all figures are multiplied by 1000 because the input file for BCP contains a comma as the decimal symbol.

Thus, instead of the data being delivered with a value of 100, it ends up having a value of 100000

Is there anyone out there who is aware of any command line parameters that can be added to the bcp command in order to avoid this ? Note, the field in question is defined as SQLCHAR in the BCP format file.

Thanks for any help anyone may be able to offer.Second try on this one, does anyone have any possible suggestions ?

thanks again.|||What's the collation set to?

Mayb if you bcp the data to a staging table with a column a varchar then use replace?

Or divide by 1000 after the load?

Never had this problem