How to concatenate all columns in a select with SQL Server

by

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>

More Related Contents:

  1. Calculating distance between two points (Latitude, Longitude)
  2. How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
  3. T-SQL split string based on delimiter
  4. WHERE Clause vs ON when using JOIN
  5. Partition Function COUNT() OVER possible using DISTINCT
  6. CREATE TABLE IF NOT EXISTS equivalent in SQL Server [duplicate]
  7. Cannot use UPDATE with OUTPUT clause when a trigger is on the table
  8. CASE WHEN statement for ORDER BY clause
  9. How to get last 7 days data from current datetime to last 7 days in sql server
  10. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  11. Reference an alias elsewhere in the SELECT list
  12. Using IF ELSE statement based on Count to execute different Insert statements
  13. How to find out what is locking my tables?
  14. Passing dynamic order by in stored procedure
  15. Select SQL Server database size
  16. Check if table exists and if it doesn’t exist, create it in SQL Server 2008
  17. How do I find the data directory for a SQL Server instance?
  18. How to return default value from SQL query
  19. How to automatically generate unique id in SQL like UID12345678?
  20. How to calculate age (in years) based on Date of Birth and getDate()
  21. How to get difference between two rows for a column field?
  22. SQL Server SELECT into existing table
  23. The parameterized query expects the parameter which was not supplied
  24. SQL Server: drop table cascade equivalent?
  25. SQL Server converting varbinary to string
  26. How do I prevent a database trigger from recursing?
  27. Executing SQL Server Agent Job from a stored procedure and returning job result
  28. Why & When should I use SPARSE COLUMN? (SQL SERVER 2008)
  29. Optimizing Delete on SQL Server
  30. Conversion failed when converting the varchar value ‘simple, ‘ to data type int

Leave a Comment