In this article, we will explore the STRING_AGG function in SQL Server and reinforce our learning with various examples. The STRING_AGG function is a new member of the SQL aggregation functions and was introduced in SQL Server 2017. It allows us to gather all expressions from rows and concatenate them into a single string, with the option to specify a separator.

How it Works

The STRING_AGG function takes all rows of a specified column and combines the expressions, adding the specified separator between them. For example, if we have a column called “Column1” with the values “If”, “you”, “want”, “a”, “happy”, “life”, “save”, and “earth”, and we use the hyphen (-) as the separator, the resulting output of the function will be “If-you-want-a-happy-life-save-earth”.

First Example

Let’s start with a simple example of the STRING_AGG function. We will create a sample table called “PersonTestTable” and populate it with some synthetic data:

DROP TABLE IF EXISTS [PersonTestTable]
GO

CREATE TABLE [PersonTestTable] (
    [FirstName] [varchar](400) NULL,
    [LastName] [varchar](400) NULL,
    [Mail] [varchar](100) NULL,
    [Country] [varchar](100) NULL,
    [Age] [int] NULL
) ON [PRIMARY]
GO

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)
GO

In this example, the STRING_AGG function will take all rows of the “FirstName” column from the “PersonTestTable” table and generate a concatenated string with these rows, separated by a hyphen (-).

SELECT FirstName FROM PersonTestTable

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

Syntax of STRING_AGG Function

The syntax of the STRING_AGG function is as follows:

STRING_AGG(expression, separator) [<order_clause>]

The “expression” parameter specifies the expressions that will be concatenated. The “separator” parameter is used to separate the expressions. The “order_clause” parameter is optional and allows us to order the concatenated expressions using the “WITHIN GROUP” statement.

Sorting Result of STRING_AGG Function

The STRING_AGG function allows us to sort the concatenated expressions in ascending or descending order. We can use the “ORDER BY” clause with the “WITHIN GROUP” statement to achieve this. For example:

SELECT FirstName FROM [PersonTestTable] ORDER BY FirstName ASC

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

In this example, the STRING_AGG function sorts the concatenated expressions in ascending order based on the values in the “FirstName” column.

Grouping Concatenated Expressions with STRING_AGG

The GROUP BY clause allows us to group rows with the same values in SQL Server. We can use the STRING_AGG function with the GROUP BY clause to generate grouped and concatenated expressions. For example:

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

In this example, we generate grouped and concatenated email addresses by the “Country” column.

Handling NULL Values

The STRING_AGG function ignores NULL values when concatenating expressions and does not add an extra separator between them. For example:

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

In this example, the NULL value does not affect the result of the function.

Alternative Method for Older Versions

If you are using an older version of SQL Server, you can use the combination of the FOR XML PATH and STUFF statements to concatenate rows expressions. Although this method is more complicated than the STRING_AGG function, it can be useful for older versions. For example:

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

In this example, we use the FOR XML PATH statement to generate an XML element from the query result. Then, we use the STUFF function to remove the first extra separator.

Generating Concatenated Rows in a Single Cell

We can use the carriage return (CHAR(13)) as the separator parameter for the STRING_AGG function to generate concatenated rows into a single cell. For example:

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

However, when viewing the result in SQL Server Management Studio (SSMS), it may not appear as expected due to the query result option. To achieve the proper visual, we need to change the “Default destination for result” option in SSMS to “Result to text”.

Removing Duplicate Values

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

SELECT STRING_AGG(Cnty, '-') FROM (
    (SELECT DISTINCT Country AS [Cnty] FROM PersonTestTable)
) AS TMP_TBL

Advanced Details

The result type of the STRING_AGG function is determined by the data type of the first expression. If the expressions are of nvarchar or varchar types, the result will be of the same type. If the expressions are of other data types that can be converted into string types (int, float, datetime, etc.), the result data type will be NVARCHAR(4000) for non-string data types.

It’s important to note that the STRING_AGG function properly sorts numbers. However, there may be implicit conversion warnings when executing the query. Implicit conversion occurs when SQL Server needs to convert one data type to another automatically during query execution.

Conclusion

In this article, we explored the STRING_AGG function in SQL Server and learned how to use it to concatenate rows expressions into a single string. We also discussed alternative methods for older versions of SQL Server and advanced details about the function. The STRING_AGG function is a powerful tool that simplifies the concatenation of expressions in SQL queries.