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

select STUFF((select ','+ convert(varchar,l.Subject) from tbl_Student B,tbl_StudentMarks L
where B.Id=L.Id FOR XML PATH('')),1,1,'') Subject FROM tbl_Student A where A.Id=10 

Answer 2

I Think You Should try This

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

Answer 3

This could stand to be cleaned up some, but you can do this by using the metadata stored in sys.objects and sys.columns along with dynamic SQL. Note that I am NOT a fan of dynamic SQL, but for reporting purposes it shouldn't be too much of a problem.

Some SQL to create test data:

if (object_id('test') is not null)
drop table test;

create table test
(
id uniqueidentifier not null default newId()
,col0 nvarchar(255)
,col1 nvarchar(255)
,col2 nvarchar(255)
,col3 nvarchar(255)
,col4 nvarchar(255)
);

insert into test (col0,col1,col2,col3,col4)
select 'alice','bob','charlie','dave','emily'
union
select 'abby','bill','charlotte','daniel','evan'

A stored proc to build CSV rows:

-- emit the contents of a table as a CSV.
-- @table_name: name of a permanent (in sys.objects) table 
-- @debug: set to 1 to print the generated query
create procedure emit_csv(@table_name nvarchar(max), @debug bit = 0)
as

    declare @object_id int;

    set nocount on;

    set @object_id = object_id(@table_name);

    declare @name nvarchar(max);
    declare db_cursor cursor for
        select name 
        from sys.columns 
        where object_id = @object_id;

    open db_cursor;
    fetch next from db_cursor into @name

    declare @query nvarchar(max);
    set @query = '';

    while @@FETCH_STATUS = 0
    begin
        -- TODO: modify appended clause to escape commas in addition to trimming
        set @query = @query + 'rtrim(cast('+@name+' as nvarchar(max)))'
        fetch next from db_cursor into @name;

        -- add concatenation to the end of the query. 
        -- TODO: Rearrange @query construction order to make this unnecessary
        if (@@fetch_status = 0)
            set @query = @query + ' + '','' +'
    end;


    close db_cursor;
    deallocate db_cursor;

    set @query = 'select rtrim('+@query+')  as csvrow from '+@table_name;
    if @debug != 0
    begin
        declare @newline nvarchar(2);
        set @newline = char(13) + char(10)
        print 'Generated SQL:' + @newline + @query + @newline + @newline;
    end

    exec (@query);

For my test table, this generates the query:

select 
rtrim(rtrim(cast(id as nvarchar(max))) 
+ ',' 
+rtrim(cast(col0 as nvarchar(max))) 
+ ',' 
+rtrim(cast(col1 as nvarchar(max))) 
+ ',' 
+rtrim(cast(col2 as nvarchar(max))) 
+ ',' 
+rtrim(cast(col3 as nvarchar(max))) 
+ ',' 
+rtrim(cast(col4 as nvarchar(max))))  
as csvrow 
from test

and the result set:

csvrow
-------------------------------------------------------------------------------------------
EEE16C3A-036E-4524-A8B8-7CCD2E575519,alice,bob,charlie,dave,emily
F1EE6C84-D6D9-4621-97E6-AA8716C0643B,abby,bill,charlotte,daniel,evan

Suggestions

1. Modify the cursor loop to escape commas
2. Make sure that @table_name refers to a valid table (if object_id(@table_name) is null) in the sproc
3. Some exception handling would be good
4. Set permissions on this so that only the account that runs the report can execute it. String concatenation in dynamic SQL can be a big security hole, but I don't see another way to do this.
5. Some error handling to ensure that the cursor gets closed and deallocated might be nice.

This can be used for any table that is not a #temp table. In that case, you'd have to use sys.objects and sys.columns from tempdb...

Answer 4

There is a fancy way to this using SQL Server's XML functions, but for starters could you just cast the contents of the columns you care about as varchar and concatenate them with commas?

SELECT cast(colA as varchar)+', '+cast(colB as varchar)+', '+cast(colC as varchar)
FROM table

Note, that this will get tripped up if any of your contents have a comma or double quotes in them, in which case you can also use a replace function on each cast to escape them.

Answer 5

Assuming you know what columns the table has, and you don't want to do something dynamic and crazy, you can do this

SELECT CONCAT(ColumnA, ',', ColumnB) AS ColumnZ
FROM Table