T-SQL : Generic User Defined Function to return comma delimited string

Hello Forum,

How are you guys doing?

I am hoping I get some inputs into how to create a generic user defined scalar function which will take in a column of values and return a comma delimited string of value.

For example if i pass in table 1 values I should get output like below

INPUT

Output String

Apple, Orange, Pear, Strawberry



Can we use "Stuff" for this? I wanted to make this function generic since I have to do this very often in most of my ssrs reports.

Thanks a lot guys, Your time and suggestions are much appreciated.


-Shilpa.

Shilpa,

Here are two links that will help you with what you need.

http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=createacommadelimitedlist

And another from Penal Dave where he gives a solution like this:

USE AdventureWorks
GO
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + Name
FROM Production.Product
SELECT @listStr
GO

Good luck and please mark as answered if this is a good solution for you. :)

Brian K. McDonald, SQLBIGeek

What an excellent idea for a function, and a great response.  Thanks.