Generic User Defined Function to return comma delimited string

Question

Post reply

Generic User Defined Function to return comma delimited string

sharonrao123

Default port

Points: 1450
More actions
April 10, 2011 at 6:16 pm

#238345

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

Apple

Pear

Orange

Orange

Pear

Strawberry

Output

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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

A snippet of code from Jeff Modens article, which can be accessed by clicking on the first link in my signature bloxk

SELECT 'SELECT ' + QUOTENAME(ID,'''')+',' + QUOTENAME(DateValue,'''')+',' + QUOTENAME(Value,'''')+',' + QUOTENAME(YearValue,'''')+',' + QUOTENAME(MonthValue,'''') + ' UNION ALL' FROM yourtable

If you do not want the quotation marks then adjust accordingly.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

sharonrao123 (4/10/2011)

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

Apple

Pear

Orange

Orange

Pear

Strawberry

Output

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.

I wouldn't consider trying to make a generic function do this. It's too easy to do in in-line code. Please see the following article on how to do this...

http://qa.sqlservercentral.com/articles/comma+separated+list/71700/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

sharonrao123 Default port Points: 1450 More actions · Answer 3

Hi Jeff & Bit Bucket,

Thanks for the reply, Jeff I am using Stuff at the moment but I have around 5 UDF's for each table which generate a comma delimeted string and take in primary key as parameter. So I thought instead creating the function for every requirement which gives the same output, I thought to do a generic funciton. But you have suggested creating a generic function is not a good approach why is that?

Thanks, Shilpa.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

sharonrao123 (4/10/2011)

But you have suggested creating a generic function is not a good approach why is that?

Thanks, Shilpa.

First, how would you make it generic without using dynamic SQL which cannot be done in a function?

Second, did you read the article I provided a link for? It's really simple to create CSV's using that method. So simple that you might wonder why you wanted a generic function to begin with.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions