How to concatenate all columns in a select with SQL Server
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>
SQL Fiddle
MS SQL Server 2008 Schema Setup:
create table YourTable
(
ID int identity primary key,
Name varchar(50),
)
insert into YourTable values
('Name 1'),
('Name 2'),
('Name 3'),
('Name 4'),
('Name 5')
Query 1:
select (
select (
select ', '+T2.N.value('./text()[1]', 'varchar(max)')
from (
select T.*
for xml path(''), type
) as T1(N)
cross apply T1.N.nodes('/*') as T2(N)
for xml path(''), type
).value('substring(./text()[1], 3)', 'varchar(max)')
for xml path('text'), type
)
from YourTable as T
Results:
| COLUMN_0 |
--------------------------
| <text>1, Name 1</text> |
| <text>2, Name 2</text> |
| <text>3, Name 3</text> |
| <text>4, Name 4</text> |
| <text>5, Name 5</text> |