Return comma seperated value

[Siva73]

Hi
I want to return a set of value with comma seperated in a single row with out using cursorts.

Eg.

Col 1
A
A
B

I want the result to be A,B.

Is there any SQL Server built in function does this?
Appreciate your help.

Thanks

[Mam139]

Hi
I want to return a set of value with comma seperated in a single row with out using cursorts.

Eg.

Col 1
A
A
B

I want the result to be A,B.

Is there any SQL Server built in function does this?
Appreciate your help.

Thanks

Yes, there is a function called coalesce which works fro your problem.

Try this.

Declare @Var Varchar(10)
Select @Var = COALESCE(@Var+',' , ' ') + Col1 FROM Tab_Name
SELECT @Var


Have a nice day

[Jim Doherty]

Hi
I want to return a set of value with comma seperated in a single row with out using cursorts.

Eg.

Col 1
A
A
B

I want the result to be A,B.

Is there any SQL Server built in function does this?
Appreciate your help.

Thanks

Create the below function in the pubs database so that you can see its results first and then you can amend it to suit your requirement....

CREATE FUNCTION dbo.UDF_ConcatToLine  (@proxy char(1))
RETURNS varchar(5000)
AS
BEGIN
DECLARE @Concat varchar(5000), @Delimiter char
SET @Delimiter = ','
SELECT @Concat = COALESCE(@Concat + @Delimiter, '') + LTRIM(STR(royaltyper))
     FROM  (SELECT DISTINCT royaltyper 
            FROM dbo.titleauthor) derived
  RETURN ( SELECT @Concat AS [My_Single_Line])
END

Use it in an SQL statement like this

SELECT DISTINCT dbo.UDF_ConcatToLine('') AS [My Single Line]
FROM         dbo.titleauthor

Replace the bold elements outlined above with your field name and table name

Its somewhat specific but you could amend it eventually to feed in the fieldname name and tablename to make it generic

Regards

Jim