SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


String Concatenation in SQL Server 2017 with String_Agg Function

For string concatenation in SQL Server, developers can use string aggregation String_Agg function with SQL Server 2017. Before SQL Server 2017 String_Agg string function to concatenate string values, either CONCAT string function or "+" expression was used. If the SQL developer wants to concatenate text column values of different rows of a table, then user-defined string concatenation SQL functions were developed.

Finally, with SQL Server 2017 Transact-SQL has introduced its own string concatenation aka string aggregation functions STRING_AGG


String Concatenation Function String_Agg Syntax

If SQL programmers analyze the String_Agg SQL string function syntax, we can easily say that:
New SQL Server string concatenation function STRING_AGG concatenates string expressions by seperating each string piece with a seperator expression.

STRING_AGG ( expression, separator ) ...
WITHIN GROUP ( ORDER BY order_by_expression_list [ ASC | DESC ] )
Code

What is an additional function with String_Agg string concatenation syntax is the optional Within Group clause.
Within Group clause enables T-SQL programmers to order the string expressions according to order by expression list, in ascending or descending order.
Within Group clause requires the use of Group By clause to concatenate character column values grouped according to the unique values of the column defined in Group By clause

After we talked on String_Agg function syntax, let's make some SQL samples to understand how developers can use this new SQL Server 2017 function.


Concatenating String Values using String_Agg SQL Server 2017 Function

Let's create some sample data to work with SQL string concatenation function string_agg on SQL Server 2017 sample database

drop table if exists kodyaz_NewFunctions
create table kodyaz_NewFunctions (
 id int identity(1,1) primary key,
 sql_version varchar(100),
 function_type varchar(100),
 function_name nvarchar(100)
)
insert into kodyaz_NewFunctions select 'SQL Server 2016','String','STRING_SPLIT'
insert into kodyaz_NewFunctions select 'SQL Server 2017','String','STRING_AGG'
insert into kodyaz_NewFunctions select 'SQL Server 2017','String','TRANSLATE'
insert into kodyaz_NewFunctions select 'SQL Server 2017','String','TRIM'
insert into kodyaz_NewFunctions select 'SQL Server 2016','Rowset','OPENJSON'
insert into kodyaz_NewFunctions select 'SQL Server 2018',NULL,NULL
Code

Let's simply concatenate string column function_name using comma as seperator expression

select STRING_AGG(function_name, ',') from kodyaz_NewFunctions

-- output is:
STRING_SPLIT,STRING_AGG,TRANSLATE,TRIM,OPENJSON
Code

By the way, most common seperators used frequently by SQL programmers are "," comma, ";" semi-column and "|" pipe characters.

SQL string concatenation with SQL Server 2017 string_agg function

Of course, according to the requirements of the SQL developer, it is easy to use a different seperator expression

select
 STRING_AGG(function_name, ',' + CHAR(13)) as concatenated_string
from kodyaz_NewFunctions
Code

Here is the output prepared for an HTML document

concatenate string in SQL Server 2017 with String_Agg function

SQL developers can concatenate string columns of rows grouped by according to the Group By clause as seen in following sample query

select
 sql_version,
 STRING_AGG(function_name, ', ') as [SQL Functions]
from kodyaz_NewFunctions
group by sql_version
Code

SQL Server concatenate string function with Group By clause

At this point, if the SQL developers want to order the values in the concatenated string following syntax used with SQL windowing comes to mind at first

select
sql_version,
STRING_AGG(function_name, ',') OVER (PARTITION BY sql_version Order By function_name)
from NewFunctions
Code

Unfortunately, the output of the above SQL query will be an exception

Msg 4113, Level 15, State 1, Line 35
The function 'STRING_AGG' is not a valid windowing function, and cannot be used with the OVER clause.

SQL programmers are lucky to use the Within Group optional clause to order string fragments used in String_Agg string concatenation SQL function.

select
 sql_version,
 STRING_AGG(function_name, ',') Within Group (Order By function_name Desc)
from NewFunctions
group by sql_version
Code

order string pieces in SQL concatenation function

Let's now return to the first example query where all function_name column values are concatenated into a single SQL string value.
As a SQL programmers I simply want to order all values without any group information.
Let's try following SQL SELECT query

select
 STRING_AGG(function_name, ', ') Within Group (Order By function_name Desc)
from NewFunctions
Code

You see, we don't need to use a Group By clause with Within Group(Order By ) clause for SQL STRING_AGG function

SQL Server 2017 string concatenation function String_Agg


SQL String Concatenation Sample with NULL Values

Here is sample data for SQL query where an emailing list will be created and email addresses will be concatenated into one string variable seperated with ";" semi-column as emailing programs expect.
In this example, we will test how String_Agg SQL Server 2017 string concatenation function works with NULL column values.

drop table if exists [kodyaz_users]
create table [kodyaz_users] (
 id int identity(1,1),
 username varchar(20),
 company varchar(100),
 email varchar(100)
)
insert into [kodyaz_users] select 'kodyaz','kodyaz','kodyaz@kodyaz.com'
insert into [kodyaz_users] select 'sqlcode','sqlcode','sqlcode@sqlcode.com'
insert into [kodyaz_users] select 'sqlman','kodyaz','sqlman@kodyaz.com'
insert into [kodyaz_users] select 'sqldev','kodyaz','sqldeveloper@kodyaz.com'
insert into [kodyaz_users] select NULL,'sqlcode',NULL
insert into [kodyaz_users] select 'sqlguru','developsql','sqlguru@developsql.com'
Code

It is important that NULL values in SQL string_agg string concatenation function is ignored

select
 company,
 STRING_AGG(email,'; ') Within Group (Order By id desc) as [email list]
from users
group by company
Code

As seen in following screenshot of the query output, the NULL entries does not affect the output

string concatenation function String_Agg with NULL values on SQL Server 2017

If we tried to concatenate two string values one of which was NULL using "+" addition expression, the output would be NULL as well


Let's continue our sample using SQL concatenation function other SQL aggregation functions like Count() etc.

SELECT
 company,
 COUNT(*) as [count],
 STRING_AGG(email,'; ') WITHIN GROUP (ORDER BY id) as [emails]
FROM [kodyaz_users]
GROUP BY company;
Code

You'll realize when you look at the last line. Altough the count is 2, there is only one email address in the concatenated string.
This difference is because of the NULL column values in our sample data.
Count() function calculates the NULLs too. But string concatenation function String_Agg does not show any value for the NULL strings.

string concatenation function and other SQL aggregation functions



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.