The requirement here is to concatenate multiple rows into a single string, not simply to do a concatenation of columns within a row, which is easy with the the “+” operator or T-Sql’s CONCAT function.

Concatenation of rows is now very straightforward – I believe the change came in with Sql 2012. Why would you want to do this? For me, it’s usually when I’m building up a comma-separated column list for a dynamic sql string; I can’t think of many other requirements. A column list string won’t be very long (we hope), so there’s no real need to consider performance. For completeness I will look at issues around the length of strings returned though.

The basic statement for a column list is as simple as this:

DECLARE @Names varchar(8000) = '' --you'll need this default if the CONCAT_NULL_YIELDS_NULL setting in your database is ON.
SELECT @Names+= name + ','
FROM sys.all_columns ac
WHERE ac.object_id = object_id('sys.all_objects')
--row sort determines order of concatenation--
ORDER BY column_id
--easier to trim off trailing delimiter here rather than in sql statement--
SET @Names = LEFT(@Names, LEN(@Names)-1)


--all done, check length of result--
PRINT LEN(@Names)
--view result--
PRINT @Names


For column lists there won’t be any Null values, but if you do have to cater for them then change the assignment to SELECT @Names+= COALESCE(name + ',', ''). The second value can be whatever you want of course.

In relation to issues with length of column names and of strings: if the length of the concatenation is too big for your variable you won’t get an error, so declare the variable to a generous size.

In relation to column lists, please note that sys.all_columns.name is now defined as “nvarchar(128)” – equating to sysname – which reduces the number of characters returned:

DECLARE @Names varchar(8000) = ''
SELECT @Names+= name
FROM sys.all_columns ac
ORDER BY name
--trim off trailing comma--
SET @Names = LEFT(@Names, LEN(@Names)-1)
--check length--
PRINT LEN(@Names)
--result--
PRINT @Names


If you don’t need unicode, cast the column values to varchar(128):

DECLARE @Names varchar(8000) = ''
SELECT @Names+= CAST(name as varchar(128))
FROM sys.all_columns ac
ORDER BY name
--trim off trailing comma--
SET @Names = LEFT(@Names, LEN(@Names)-1)
--check length--
PRINT LEN(@Names)
--result--
PRINT @Names

and of course you’d be able to handle the maximum amount of data by redefining the variable as varchar(max),  although at that point you might ask yourself if your design is really heading in the right direction:

DECLARE @Names varchar(max) = ''
SELECT @Names+= CAST(name as varchar(128))
FROM sys.all_columns ac
ORDER BY name
--trim off trailing comma--
SET @Names = LEFT(@Names, LEN(@Names)-1)
--check length--
PRINT LEN(@Names)
--result--
PRINT @Names

To begin with this syntax looked really weird to me, and it doesn’t seem like it conforms to any SQL standard (or actually is SQL for that matter) but it’s concise and seems like a very useful vendor extension to me.