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

[Not Me]

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

[David Rawheiser]

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