Return multiple columns as single comma separated row in SQL Server 2005

Question

I'm curious to see if this is possible.

I have a table or this could be specific to any old table with data. A simple SELECT will return the columns and rows as a result set. What I'm trying to find out if is possible to return rows but rather than columns, the columns concatenated and are comma separated. So expected amount of rows returned but only one varchar column holding comma separated results of all the columns just like a CSV file.

Thanks.

[UPDATE]

Here is a bit more detail why I'm asking. I don't have the option to do this on the client, this is a task I'm trying to do with SSIS.

Scenario: I have a table that is dynamically created in SSIS but the column names change each time it's built. The original package uses BCP to grab the data and put it into a flat file but due to permissions when run as a job BCP can't create the flat file at the required destination. We can't get this changed either.

The other issue is that with SSIS 2005, using the flat files destination, you have to map the column name from the input source which I can't do because the column names keep changing.

I've written a script task to grab all the data from the original tables and then use stream writer to write to the CSV but I have to loop through each row then through each column to produce the string built up of all the columns. I want to measure performance of this concatenation of columns on sql server against a nasty loop with vb.net.

If I can get sql to produce a single column for each row I can just write a single line to the text file instead of iterating though each column to build the row.

Answer 1

I Think You Should try This

    SELECT UserName +','+ Password AS ColumnZ
FROM UserTable