469,631 Members | 1,817 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,631 developers. It's quick & easy.

Return comma seperated value

1
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
Sep 4 '07 #1
2 2154
Mam139
2
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
Sep 5 '07 #2
Jim Doherty
897 Expert 512MB
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....

Expand|Select|Wrap|Line Numbers
  1. CREATE FUNCTION dbo.UDF_ConcatToLine  (@proxy char(1))
  2. RETURNS varchar(5000)
  3. AS
  4. BEGIN
  5. DECLARE @Concat varchar(5000), @Delimiter char
  6. SET @Delimiter = ','
  7. SELECT @Concat = COALESCE(@Concat + @Delimiter, '') + LTRIM(STR(royaltyper))
  8.      FROM  (SELECT DISTINCT royaltyper 
  9.             FROM dbo.titleauthor) derived
  10.   RETURN ( SELECT @Concat AS [My_Single_Line])
  11. END

Use it in an SQL statement like this

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT dbo.UDF_ConcatToLine('') AS [My Single Line]
  2. 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
Sep 7 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Pmb | last post: by
3 posts views Thread by Gary Smith | last post: by
3 posts views Thread by Maqsood Ahmed | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.