An overview of the STRING_AGG function in SQL

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:

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:

DROP TABLE IF EXISTS [PersonTestTable]

CREATE TABLE [PersonTestTable](

[FirstName] [varchar](400) NULL,

[LastName] [varchar](400) NULL,

[Mail] [varchar](100) NULL,

Country [varchar](100) NULL,

Age [int] NULL

) ON [PRIMARY]

INSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Lawrence',N'Williams',N'uhynb.ndlguey@vtq.org',N'U.S.A.',21)

INSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Gilbert',N'Miller',N'loiysr.jeoni@wptho.co',N'U.S.A.',53)

INSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Salvador',N'Rodriguez',N'tjybsrvg.rswed@uan.org',N'Russia',46)

INSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Ernest',N'Jones',N'psxkrzf.jgcmc@pfdknl.org',N'U.S.A.',48)

INSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Jerome',N'Garcia',NULL,N'Russia',46)

INSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Roland',N'Smith','xpdek.qpl@kpl.com',N'U.S.A. ',35)

INSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Stella',N'Johnson',N'qllyoxgr.jsntdty@pzwm.org',N'Russia',24)

INSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Aria',N'Anderson',N'sjgnz.voyyc@cvjg.com',N'Brazil ',25)

INSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Edward',N'Martinez','pokjs.oas@mex.com',N'Mexico ',27)

INSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Nicholas',N'Brown',N'wpfiki.hembt@uww.co',N'Russia ',43)

INSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Ray',N'Wilson',NULL,N'Russia',41)

INSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Jorge',N'Davis',N'bhlji.zwngl@kle.com',N'Russia ',49)

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:

1 2	SELECT FirstName FROM PersonTestTable SELECT STRING_AGG(FirstName,'-') AS Result FROM PersonTestTable

Syntax of STRING_AGG function in SQL

The syntax of the STRING_AGG function looks like below:

1	STRING_AGG ( expression, separator ) [ <order_clause> ]

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:

SELECT FirstName FROM [PersonTestTable] ORDER BY FirstName ASC

SELECT STRING_AGG(FirstName,'-') WITHIN GROUP ( ORDER BY FirstName ASC) AS Result FROM [PersonTestTable]

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:

SELECT Country,STRING_AGG(Mail,',') WITHIN GROUP ( ORDER BY FirstName ASC) AS

Result FROM PersonTestTable

GROUP BY Country

ORDER BY Country asc

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:

SELECT Country,Mail

Result FROM [PersonTestTable]

where country='Russia'

group by Country, Mail

SELECT Country,

STRING_AGG(Mail,',') WITHIN GROUP ( ORDER BY Mail ASC) AS

Result FROM [PersonTestTable]

where country='Russia'

group by Country

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:

1	SELECT STUFF((SELECT '-' + FirstName as [text()] FROM PersonTestTable FOR XML PATH('')),1,1,'') AS Result

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:

1	SELECT '-' + FirstName as [text()] FROM PersonTestTable FOR XML PATH

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

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

1	SELECT '-' + FirstName as [text()] FROM PersonTestTable FOR XML PATH('')

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:

1	SELECT STRING_AGG(FirstName,CHAR(13)) AS Result FROM [PersonTestTable]

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:

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

1	SELECT STRING_AGG(FirstName,CHAR(13)) AS Result FROM PersonTestTable

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:

SELECT STRING_AGG(Cnty, '-') FROM

(

(SELECT DISTINCT Country AS [Cnty] FROM PersonTestTable)

) AS TMP_TBL

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:

DROP TABLE IF EXISTS TempTableForFunction

DROP TABLE IF EXISTS TempTableForFunctionResult

CREATE TABLE TempTableForFunction

(SampleVal Float)

INSERT INTO TempTableForFunction VALUES (12.67) , (98.09),(65.42),(56.72),(129.12)

SELECT STRING_AGG(SampleVal,'-') WITHIN GROUP ( ORDER BY SampleVal ASC) AS Result INTO TempTableForFunctionResult FROM TempTableForFunction

SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TempTableForFunctionResult'

SELECT STRING_AGG(SampleVal,'-') WITHIN GROUP ( ORDER BY SampleVal ASC) AS Result FROM TempTableForFunction

Now, examine the result:

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:

1	SELECT STRING_AGG(SampleVal,'-') WITHIN GROUP ( ORDER BY SampleVal ASC) AS Result FROM TempTableForFunction

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:

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.

Author
Recent Posts

Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.

View all posts by Esat Erkec