By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,911 Members | 1,124 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,911 IT Pros & Developers. It's quick & easy.

Function to create comma separated list from any given column/table.

P: n/a
Hi,

I'm sure this is a common problem.. to create a single field from a
whole column, where each row would be separated by a comma.

I can do this for a specified table, and column.. and I've created a
function using VBA to achieve a more dynamic (and very slow) solution..
so I would like to implement it using a user defined function in sql server.

The problems I'm facing are, that I can't use dynamic sql in a
function.. and I also can't use temporary tables which could build up a
'standard' table from parameters given to then perform the function on.

So, with these limitations, what other options do I have?

Cheers,
Chris
Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Dynamic SQL is so so passe'

Firstly it is a security risk , as you can't lock down rights to procedures
that use it.

secondly it is inefficient as it has to be re-generated and compiled each
time you use it

Code Generation is the all the rage now.

And how often will you be adding new code tables to do this with?

Here's how to roll your own code generator in SQL:

in Query Analyzer (aka ISQL)

write a template code block to get it to work (I've done this below)

wrap that in print statements.

create a cursor to loop thru sysobjects and/or syscolumns (or the SCHEMA
Views) to locate all your tables with the columns you need.

loop thru and generate all the script you need.

you can create a separate function/procedure for each or create a single one
with a large case in it (your call).

run the output to create the function(s).

save the script that generates the stuff and hold on to it.

every time a change is made to a code table (i.e. add new table, change
column names, etc..) rerun the script to regenerated (make sure there is a
drop in there somewhere as well)

Tally Ho! - (which is a liquor store her in Delaware, and not a Fox hunting
term, which I understand is now outlawed in the UK).

-----------------------------------------------------------
-- declare an empty string (not null)
declare @list varchar 4000
set @list = ''

-- select multiple rows into a single variable
select @list = @list + ',' + ColumnName from TableName

-- get rid of the last comma
select @list = substring( @list , 1, len( @list ) -1 )
-----------------------------------------------------------

"Not Me" <No***********@here.com> wrote in message
news:cl**********@ucsnew1.ncl.ac.uk...
Hi,

I'm sure this is a common problem.. to create a single field from a whole
column, where each row would be separated by a comma.

I can do this for a specified table, and column.. and I've created a
function using VBA to achieve a more dynamic (and very slow) solution.. so
I would like to implement it using a user defined function in sql server.

The problems I'm facing are, that I can't use dynamic sql in a function..
and I also can't use temporary tables which could build up a 'standard'
table from parameters given to then perform the function on.

So, with these limitations, what other options do I have?

Cheers,
Chris

Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.