How to concatenate all columns in a select with SQL Server

sql, sql-server, sql-server-2008-r2

Solution

Any number of columns for a given tablename; If you need column names wrapped with `<text>`

DECLARE @s VARCHAR(500)

SELECT @s =  ISNULL(@s+', ','') + c.name   
FROM  sys.all_columns c join sys.tables  t 
            ON  c.object_id = t.object_id
WHERE t.name = 'YourTableName'

SELECT '<text>' + @s + '</text>'

SQL Fiddle Example here

-- RESULTS 
<text>col1, col2, col3,...</text>

If you need select query result set wrapped with `<text>` then;

SELECT @S =  ISNULL( @S+ ')' +'+'',''+ ','') + 'convert(varchar(50), ' + c.name    FROM 
       sys.all_columns c join sys.tables  t 
       ON  c.object_id = t.object_id
WHERE t.name = 'YourTableName'


EXEC( 'SELECT ''<text>''+' + @s + ')+' + '''</text>'' FROM YourTableName')

SQL Fiddle Example here

--RESULTS
<text>c1r1,c2r1,c3r1,...</text>
<text>c1r2,c2r2,c3r2,...</text>
<text>c1r3,c2r3,c3r3,...</text>

Problem

I need my select to have a pattern like this: ``` SELECT '<text> ' + tbl.* + ' </text>' FROM table tbl; ``` The ideal solution would have all the columns separated by a comma in order to have that output: SQL result for Table 1 with two columns: ``` '<text>col1, col2</text>' ``` SQL result for Table 2 with three columns: ``` '<text>col1, col2, col3</text>' ``` I tried to use the `CONCAT(...)` function like this: ``` SELECT CONCAT('<text>', tbl.*, '</text>') FROM table2 tbl ``` But I understand it is not so simple because the variable number of columns. Is there any simple solution to address that problem? I am using SQL Server 2008 R2.

Original source