Esat Erkec

An overview of the STRING_AGG function in SQL

January 3, 2020 by

In this article, we will explore the STRING_AGG function in SQL and we will also reinforce our learning with various examples. STRING_AGG function can be counted as a new member of the SQL aggregation functions and it was announced in SQL Server 2017. STRING_AGG function gathers all expression from rows and then puts it together in these rows expression in order to obtain a string. Additionally, this function takes a separator parameter that allows separating the expressions to be concatenated.

How it works

The following image illustrates the working mechanism of the STRING_AGG function. In this illustration, STRING_AGG function takes all rows expression from Column1 and then combines these expressions and it also adds the hyphen () sign as a separator between these expressions. The resulting output of the function will be If-you-want-a-happy-life-save-earth:

The working mechanism of the STRING_AGG function in SQL

STRING_AGG function first example

Firstly, we will start a pretty simple example of STRING_AGG function and then we will examine the syntax and other details about this function. We will create a sample table and populate it with some synthetic data so that we can use this table whole examples of the article. The following script will help to generate a sample table called PersonTestTable:

In this first example, the STRING_AGG function will take all rows expression from the FirstName column of the PersonTestTable table and then generate a concatenated string with these rows expression. At the same time, the concatenated expressions will be separated with the hyphen () sign:

STRING_AGG function example of result illustration

Syntax of STRING_AGG function in SQL

The syntax of the STRING_AGG function looks like below:

The expression parameter specifies any expressions that will be concatenated. The separator parameter is used to separate expressions that will be concatenated. The order_clause parameter is an optional parameter and helps to order the concatenated expression and it must be used with WITHIN GROUP statement.

Sorting result of STRING_AGG function in SQL

STRING_AGG function allows sorting concatenated expressions in descending or ascending order. In this example, we will sort the concatenated expressions according to the FirstName column rows expression with the WITHIN GROUP statement:

STRING_AGG function usage with WITHIN GROUP statement

As we can see clearly the STRING_AGG function sorted the concatenated expressions in the ascending order according to row values of the FirstName column. We need to underline one point about this type of usages. The GROUP BY clause will be necessary if the STRING_AGG result is not a sole column in the result set of the query. In the next section, we will learn this concept.

How to group concatenated expressions with STRING_AGG

GROUP BY clause provides grouping the rows that have the same values in SQL Server. In the following example, we will generate grouped and concatenated e-mail addresses by the Country column:

Grouping concatenated expressions with the STRING_AGG function in SQL

At this point, we have to take account of one consideration about the STRING_AGG function. The NULL values are ignored when the STRING_AGG concatenates the expressions in the rows and it also does not add an extra separator between the expressions due to NULL values. The following example will be illustrated in this case:

NULL value and STRING_AGG function interaction

As we can see, the NULL value did not affect the result of the function.

The old method that can be used instead of STRING_AGG function

If we are using an older version than SQL Server 2017, we can use FOR XML PATH and STUFF statements combinations in the query to concatenate rows expressions. However, this method is more complicated than STRING_AGG function but it can be useful for the older version of the SQL Server. The following query returns the same result as the previous sample:

STUFF and FOR XML PATH usage for string concatenation

FOR XML PATH statement provides to generate an XML element from the query result. When we execute the following query, it transforms query result to XML:

FOR XML PATH usage in SQL Server

If we click the result of the query, we can find out the XML more clearly:

Output of the FOR XML PATH

When we add the blank string option at the end of the FOR XML PATH, we will obtain concatenated and separated expressions:

FOR XML PATH concanate the strings

STUFF function helps to delete a specified part of the string and then it can add a new string to it. Finally, we will clear the first extra separator with the STUFF function.

How to generate a concatenated rows in a single cell

The carriage return allows setting the cursor to the beginning of the next line. We can provide this option in SQL with CHAR(13) statement. We will use CHAR(13) statement as a separator parameter for STRING_AGG function so that we can generate concatenated rows into a single cell:

Generating a concatenated row in a single cell

As we can see, we did not obtain the result that we wished in the SQL Server Management Studio result tab. In fact, it misleads us in SSMS because of the query result option. We will change this option in SSMS so that we achieve the proper visual. We will find the Options setting under the Tools menu and then change the Default destination for result option in the Query Results setting. We will change the Results to grids option to Result to text:

Change SSMS default destination for result

After this option changing, SSMS shows the result in the text. We will re-execute the same query in a new query window:

Generating a concatenated row in a single cell with STRING_AGG

How to remove duplicate values in STRING_AGG function

In some cases, we may need to eliminate duplicate values from the concatenated result of the STRING_AGG function. To handle this issue, we can use a two-tier query. In the first SELECT statement, we will eliminate the duplicate rows and then obtain unique values. Then, we will concatenate the unique expression with the STRING_AGG function:

Eliminating duplicates values in the STRING_AGG Function in SQL

Advanced details about STRING_AGG function in SQL

The result type of STRING_AGG is determined according to the first expression that is taken by the function. There is no doubt that the nvarchar and varchar types concatenated results will be in the same type. However, if we concatenate other datatypes which can be converted into string datatypes (int, float, datetime and etc.). The result data types will be NVARCHAR(4000) for non-string data types. In the following example, we will create a TempTableForFunction table that has only a float data type column. We will try to combine these table expressions through the STRING_AGG function and then the function result will create the TempTableForFunctionResult table. TempTableForFunctionResult column will be generated according to STRING_AGG function result datatype:

Now, examine the result:

Analyzing STRING_AGG function result data type

The following table shows the STRING_AGG function result types against expression data types:

Input expression type

STRING_AGG function result type

NVARCHAR(MAX)

NVARCHAR(MAX)

VARCHAR(MAX)

VARCHAR(MAX)

NVARCHAR(1…4000)

NVARCHAR(4000)

VARCHAR(1…8000)

VARCHAR(8000)


int, bigint, smallint, tinyint, numeric, float, real, bit, decimal,
smallmoney, money, datetime, datetime2,

NVARCHAR(4000)

At this point, we have to take account of one issue about the STRING_AGG function, it sorted the numbers properly. If we look behind at the scene of the query, it means that we will analyze the execution plan of the following query with ApexSQL Plan:

STRING_AGG function execution plan of the query

The sort operation is processed before the Stream Aggregate operation so the numbers are sorted out properly. On the other hand, there is a warning sign shown over the SELECT image. If we hover over this image, we can find out more details about this issue:

Implicit conversion

In the above image, the implicit conversion process is shown clearly. Implicit conversion occurs when the SQL Server query execution processes are required to convert one data type to another one and this process is automatically executed during the query execution. In addition, you can look at the Implicit conversion in SQL Server article to learn more details about the implicit conversion notion.

Conclusion

In this article, we explored the STRING_AGG function in SQL and completed various examples of this function. STRING_AGG is a very useful and simple function to convert rows expression into a single string. On the other hand, we can use the older version methods to solve these types of issues.

Esat Erkec
8,853 Views