469,590 Members | 2,467 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

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

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
1 9593
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.

Similar topics

1 post views Thread by Craig Keightley | last post: by
12 posts views Thread by insomniux | last post: by
5 posts views Thread by nikoromano | last post: by
7 posts views Thread by Hermann | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.