This function, introduced in SQL Server 2017, concatenates the values of string expressions and places separator values between them. It accepts three input arguments:

• String: This is an expression (usually a column name).
• Separator: This is a list of characters used as separators for concatenated strings (with a maximum size of 8,000 bytes).
• Order clause: Optionally, you can specify the order of concatenated results by using the WITHIN GROUP clause.

The function returns a string, but the exact return type depends on the input string expression (first function argument). The following table shows the return type of the function for a given input argument:

So, this function is opposite to STRING_SPLIT. It concatenates table rows into a single string. Expression values (columns) are implicitly converted to string types and then concatenated.

To see this function in action, use this code to generate a comma-separated list of database names from a test server. Run this code against a SQL Server 2017 instance hosting Microsoft sample databases:

SELECT name FROM sys.databases; 

Here is the list of databases on a freshly installed server with the sample databases:

name
------------------
master
tempdb
model
msdb
WideWorldImporters
WideWorldImportersDW

To get a comma-separated list of database names, use the following code:

SELECT STRING_AGG (name, ',') AS dbs_as_csv FROM sys.databases;

As expected, the result is a string:

dbs_as_csv
---------------------------------------------------------------
master,tempdb,model,msdb,WideWorldImporters,WideWorldImportersDW

The column does not need to be of string type. You can use a non-string column; data is automatically converted to string, as in the following example:

SELECT STRING_AGG (database_id, ',') AS dbiss_as_csv FROM sys.databases;

The result is again a comma-separated string of database IDs:

dbids_as_csv
-------------
1,2,3,4,5,6

Since the input column is a sysname data type (which is equivalent to nvarchar(128) according to the table Return type of the STRING_AGG function), the function returns a value of the nvarchar(4000) data type. That means that you can have up to 2,000 characters in the resulting string, including separators.

Therefore, this code, showing all columns in the WideWorldImporters database, will fail:

USE WideWorldImporters;
SELECT STRING_AGG (name, ',') AS cols FROM sys.syscolumns; 

 Instead of a comma-separated list, you will see the following error message:

Msg 9829, Level 16, State 1, Line 1
STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation.

To show a comma-separated list of all database columns, you need to convert the data type of the name column:

USE WideWorldImporters;
SELECT STRING_AGG (CAST(name AS NVARCHAR(MAX)), ',') AS cols FROM sys.syscolumns;

This will produce the expected output—a comma-separated list of all column names in a given database:

cols
----------------------------------
bitpos,cid,colguid,hbcolid,maxinrowlen,nullbit,offset,ordkey,rcmodified,rscolid,rsid...

The list is, of course, abbreviated, since there are almost 1,700 columns in this database.