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

I Think You Should try This

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

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

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.

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

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