generic stored proc/function to convert columns to concatenated rows

Question

generic stored proc/function to convert columns to concatenated rows

aye_taur

SSC Veteran

Points: 247
More actions
September 25, 2008 at 1:21 pm

#198582

I want to write a common function or stored procedure to which I want to pass as variables, table name and delimiter. This function/stored procedure should be able to concatenate the table columns separated by the delimiter and insert it into a second table. The source table can have any number of columns and any number of rows. The columns may also contain null values and will be of varchar data types. Any idea on how to do this effectively will be greatly appreciated.

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

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

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 1

I'd pass in the stuff you have, then maybe query information_schema.columns for the table name, get the columns. You can concatenate those in a query and then build a string that can be executed with Exec()

Course depending on how many things you need to do this for, I'd honestly use this as a script to build a proc for every table. Works as well, you have separation for each table, can't get someone inserting to the wrong table because of permission, etc.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

Along with what Steve said - a function won't do this for you, so focus on using a stored proc. Functions can't use EXEC() and cannot make changes to the DB, so at best you could use one to build the query string, but you oculdn't execute it.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

aye_taur SSC Veteran Points: 247 More actions · Answer 3

Unfortunately, my requirement is to specifically build a generic stored proc which can be called by n number of stored procs who will pass the table name and the delimiter. Since I am passing a temporary table name, I used tempdb.sys.objects and tempdb.sys.columns to obtain the object_id and thereby the column names which I put in another temporary table #T.

Now I am trying to use a while loop to take a particular column name from the table #T and use it to fetch a value from the actual table. But beyond inserting the first column values and adding the delimiter after that, I am not sure how to proceed. This is what I have till now for the while loop:

SELECT @TOTAL_COUNT = COUNT(*) FROM #T

SET @ROW =1

WHILE @ROW < @TOTAL_COUNT

SELECT TOP(@ROW) @NAME = COLS FROM #T

SELECT @s-2 = 'INSERT INTO S(DTA) SELECT ' + @NAME + ' +''' + @DELIMITER + '''' +

' FROM ' + @TBLE_NAME

EXEC(@S)

SET @ROW = @ROW +1

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

Heh... "Loop" and "Lose" are both 4 letter words beginning with "L". 😛 Please see the following article for how to perform concatenation and some of the pitfalls to avoid.

http://www.sqlservercentral.com/articles/Test+Data/61572/

--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)