Concatenating rows from different tables into one field

Question

Hi!

In a project using a MSSQL 2005 Database we are required to log all data manipulating actions in a logging table. One field in that table is supposed to contain the row before it was changed. We have a lot of tables so I was trying to write a stored procedure that would gather up all the fields in one row of a table that was given to it, concatenate them somehow and then write a new log entry with that information.

I already tried using FOR XML PATH and it worked, but the client doesn't like the XML notation, they want a csv field.

Here's what I had with FOR XML PATH:

DECLARE @foo varchar(max);
SET @foo = (SELECT * FROM table WHERE id = 5775 FOR XML PATH(''));

The values for "table", "id" and the actual id (here: 5775) would later be passed in via the call to the stored procedure.

Is there any way to do this without getting XML notation and without knowing in advance which fields are going to be returned by the SELECT statement?

Answer 1

We used XML path and as you've discovered, it works very well. Since one of SQL's features is to store XML properly, the CSV makes no sense.

What you could try is a stored proc that reads out the XML in CSV format (fake it). I would. Since you won't likely be reading the data that much compared to saving it, the overhead is negligible.

Answer 2

How about:

Set @Foo = Stuff(
                        ( Select ',' + MyCol1 + ',' + MyCol2 ...
                            From Table
                            Where Id = 5775 
                            For Xml Path('') 
                        ), 1, 1, '')

This will produce a CSV line (presuming the inner SQL returns a single row). Now, this solves the second part of your question. As to the first part of "without knowing in advance which fields", there is no means to do this without using dynamic SQL. I.e., you have to build the SQL statement as a string on the fly. If you are going to do that you might as well build the entire CSV result on the fly.