String Aggregation on SQL Server using String_Agg Function

Finally SQL developers coding on SQL Server have now a handy tool STRING_AGG function for string aggregation with SQL Server 2017.

Up to now, Transact-SQL programmers have created many user-defined functions and build new methods for string concatenation on SQL Server databases.

For example, using For XML Path clause enables the result of an XML query to be converted into a string value seperated with a given character.

A prior solution to string concatenation was using SQL COALESCE function. Developers will find a sample for Coalesce SQL function in the referecend tutorial too.

Now, SQL developers can use STRING_AGG function to concatenate string values seperated with a given seperator

For example, let's assume that you want to display tables and its columns list by means of a SQL query.

Here is your SQL query for this task:

select
    TABLE_NAME,
    string_agg(COLUMN_NAME,', ') Within Group (Order By ordinal_position) as column_list
from [INFORMATION_SCHEMA].[COLUMNS]
where TABLE_CATALOG = 'kodyaz'
group by TABLE_SCHEMA, TABLE_NAME

The output on my sample database kodyaz is as follows:

You see, all the columns of a table are concatenated to form the column_list value seperated with comma

For more on string_agg SQL function, database developers can refer to String Concatenation in SQL Server 2017 with String_Agg Function as well as the official reference document.

Maybe, as a SQL developer you have realized the GROUP BY clause in the SELECT query and the WITHIN GROUP clause of String_Agg function. This usage or this SQL syntax is important, because it enables developers to concatenate group based.

If you try to use PARTITION BY clause just like we do on many other aggregation functions or window functions, SQL Engine will throw an exception. Just take care of WITHIN GROUP usage of String_Agg SQL function,

Happy querying,