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 ] )
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
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
By the way, most common seperators used frequently by SQL programmers are "," comma, ";" semi-column and "|" pipe characters.
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
Here is the output prepared for an HTML document
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
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
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
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
You see, we don't need to use a Group By clause with Within Group(Order By ) clause for SQL STRING_AGG function
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'
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
As seen in following screenshot of the query output, the NULL entries does not affect the output
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;
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.
