--- title: "STRING_AGG (Transact-SQL)" description: STRING_AGG concatenates the values of string expressions and places separator values between them." author: MikeRayMSFT ms.author: mikeray ms.reviewer: randolphwest ms.date: 01/16/2025 ms.service: sql ms.subservice: t-sql ms.topic: reference f1_keywords: - "STRING_AGG" - "STRING_AGG_TSQL" helpviewer_keywords: - "STRING_AGG function" dev_langs: - "TSQL" monikerRange: "=azuresqldb-current || =azure-sqldw-latest || >=sql-server-2017 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric" --- # STRING_AGG (Transact-SQL) [!INCLUDE [sqlserver2017-asdb-asdbmi-asa-fabricse-fabricdw](../../includes/applies-to-version/sqlserver2017-asdb-asdbmi-asa-fabricse-fabricdw.md)] Concatenates the values of string expressions and places separator values between them. The separator isn't added at the end of string. :::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: [Transact-SQL syntax conventions](../../t-sql/language-elements/transact-sql-syntax-conventions-transact-sql.md) ## Syntax ```syntaxsql STRING_AGG ( expression , separator ) [ ] ::= WITHIN GROUP ( ORDER BY [ ASC | DESC ] ) ``` ## Arguments #### *expression* An [expression](../../t-sql/language-elements/expressions-transact-sql.md) of any type. Expressions are converted to **nvarchar** or **varchar** types during concatenation. Non-string types are converted to **nvarchar** type. #### *separator* An [expression](../../t-sql/language-elements/expressions-transact-sql.md) of **nvarchar** or **varchar** type that is used as separator for concatenated strings. It can be literal or variable. #### Optionally specify order of concatenated results using `WITHIN GROUP` clause: ```syntaxsql WITHIN GROUP ( ORDER BY [ ASC | DESC ] ) ``` - `` A list of non-constant [expressions](../../t-sql/language-elements/expressions-transact-sql.md) that can be used for sorting results. Only one `` is allowed per query. The default sort order is ascending. ## Return types Return type depends on first argument (expression). If input argument is string type (**nvarchar**, **varchar**), the result type is the same as the input type. The following table lists automatic conversions: | Input expression type | Result | | --- | --- | | **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)** | ## Remarks `STRING_AGG` is an aggregate function that takes all expressions from rows and concatenates them into a single string. Expression values are implicitly converted to string types and then concatenated. The implicit conversion to strings follows the existing rules for data type conversions. For more information about data type conversions, see [CAST and CONVERT](../../t-sql/functions/cast-and-convert-transact-sql.md). If the input expression is type **varchar**, the separator can't be type **nvarchar**. Null values are ignored and the corresponding separator isn't added. To return a place holder for null values, use the `ISNULL` function as demonstrated in [example B](#b-generate-list-of-names-separated-with-comma-without-null-values). `STRING_AGG` is available in any compatibility level. > [!NOTE] > `` is available with database compatibility level 110 and above. ## Examples [!INCLUDE [article-uses-adventureworks](../../includes/article-uses-adventureworks.md)] ### A. Generate list of names separated in new lines The following example produces a list of names in a single result cell, separated with carriage returns. ```sql USE AdventureWorks2022; GO SELECT STRING_AGG(CONVERT (NVARCHAR (MAX), FirstName), CHAR(13)) AS csv FROM Person.Person; GO ``` [!INCLUDE [ssResult_md](../../includes/ssresult-md.md)] ```output csv ----------- Syed Catherine Kim Kim Kim Hazem ... ``` `NULL` values found in `name` cells aren't returned in the result. > [!NOTE] > If using the [!INCLUDE [ssManStudioFull](../../includes/ssmanstudiofull-md.md)] Query Editor, the **Results to Grid** option can't implement the carriage return. Switch to **Results to Text** to see the result set properly. > Results to Text are truncated to 256 characters by default. To increase this limit, change the **Maximum number of characters displayed in each column** option. ### B. Generate list of names separated with comma without `NULL` values The following example replaces null values with 'N/A' and returns the names separated by commas in a single result cell. ```sql USE AdventureWorks2022; GO SELECT STRING_AGG(CONVERT (NVARCHAR (MAX), ISNULL(FirstName, 'N/A')), ',') AS csv FROM Person.Person; GO ``` Here's a trimmed result set. ```output csv ----- Syed,Catherine,Kim,Kim,Kim,Hazem,Sam,Humberto,Gustavo,Pilar,Pilar, ... ``` ### C. Generate comma-separated values ```sql USE AdventureWorks2022; GO SELECT STRING_AGG(CONVERT (NVARCHAR (MAX), CONCAT(FirstName, ' ', LastName, '(', ModifiedDate, ')')), CHAR(13)) AS names FROM Person.Person; GO ``` Here's a trimmed result set. ```output names ------- Ken Sánchez (Feb 8 2003 12:00AM) Terri Duffy (Feb 24 2002 12:00AM) Roberto Tamburello (Dec 5 2001 12:00AM) Rob Walters (Dec 29 2001 12:00AM) ... ``` > [!NOTE] > If using the Management Studio Query Editor, the **Results to Grid** option can't implement the carriage return. Switch to **Results to Text** to see the result set properly. ### D. Return news articles with related tags Imagine a database where articles and their tags are separated into different tables. A developer wants to return one row per each article with all associated tags. The following query achieves this result: ```sql SELECT a.articleId, title, STRING_AGG(tag, ',') AS tags FROM dbo.Article AS a LEFT OUTER JOIN dbo.ArticleTag AS t ON a.ArticleId = t.ArticleId GROUP BY a.articleId, title; GO ``` [!INCLUDE [ssResult_md](../../includes/ssresult-md.md)] | articleId | title | tags | | --- | --- | --- | | `172` | `Polls indicate close election results` | `politics,polls,city council` | | `176` | `New highway expected to reduce congestion` | `NULL` | | `177` | `Dogs continue to be more popular than cats` | `polls,animals` | > [!NOTE] > The `GROUP BY` clause is required if the `STRING_AGG` function isn't the only item in the `SELECT` list. ### E. Generate list of emails per towns The following query finds the email addresses of employees and groups them by city: ```sql USE AdventureWorks2022; GO SELECT TOP 10 City, STRING_AGG(CONVERT (NVARCHAR (MAX), EmailAddress), ';') AS emails FROM Person.BusinessEntityAddress AS BEA INNER JOIN Person.Address AS A ON BEA.AddressID = A.AddressID INNER JOIN Person.EmailAddress AS EA ON BEA.BusinessEntityID = EA.BusinessEntityID GROUP BY City; GO ``` [!INCLUDE [ssResult_md](../../includes/ssresult-md.md)] > [!NOTE] > Results are shown trimmed. | City | emails | | --- | --- | | `Ballard` | `paige28@adventure-works.com`;`joshua24@adventure-works.com`;`javier12@adventure-works.com`;... | | `Baltimore` | `gilbert9@adventure-works.com` | | `Barstow` | `kristen4@adventure-works.com` | | `Basingstoke Hants` | `dale10@adventure-works.com`;`heidi9@adventure-works.com` | | `Baytown` | `kelvin15@adventure-works.com` | | `Beaverton` | `billy6@adventure-works.com`;`dalton35@adventure-works.com`;`lawrence1@adventure-works.com`;... | | `Bell Gardens` | `christy8@adventure-works.com` | | `Bellevue` | `min0@adventure-works.com`;`gigi0@adventure-works.com`;`terry18@adventure-works.com`;... | | `Bellflower` | `philip0@adventure-works.com`;`emma34@adventure-works.com`;`jorge8@adventure-works.com`;... | | `Bellingham` | `christopher23@adventure-works.com`;`frederick7@adventure-works.com`;`omar0@adventure-works.com`;... | Emails returned in the emails column can be directly used to send emails to group of people working in some particular cities. ### F. Generate a sorted list of emails per towns Similar to the previous example, the following query finds the email addresses of employees, groups them by city, and sorts the emails alphabetically: ```sql USE AdventureWorks2022; GO SELECT TOP 10 City, STRING_AGG(CONVERT (NVARCHAR (MAX), EmailAddress), ';') WITHIN GROUP (ORDER BY EmailAddress ASC) AS Emails FROM Person.BusinessEntityAddress AS BEA INNER JOIN Person.Address AS A ON BEA.AddressID = A.AddressID INNER JOIN Person.EmailAddress AS EA ON BEA.BusinessEntityID = EA.BusinessEntityID GROUP BY City; GO ``` [!INCLUDE [ssResult_md](../../includes/ssresult-md.md)] > [!NOTE] > Results are shown trimmed. | City | Emails | | --- | --- | | `Barstow` | `kristen4@adventure-works.com` | | `Basingstoke Hants` | `dale10@adventure-works.com`;`heidi9@adventure-works.com` | | `Braintree` | `mindy20@adventure-works.com` | | `Bell Gardens` | `christy8@adventure-works.com` | | `Byron` | `louis37@adventure-works.com` | | `Bordeaux` | `ranjit0@adventure-works.com` | | `Carnation` | `don0@adventure-works.com`;`douglas0@adventure-works.com`;`george0@adventure-works.com`;... | | `Boulogne-Billancourt` | `allen12@adventure-works.com`;`bethany15@adventure-works.com`;`carl5@adventure-works.com`;... | | `Berkshire` | `barbara41@adventure-works.com`;`brenda4@adventure-works.com`;`carrie14@adventure-works.com`;... | | `Berks` | `adriana6@adventure-works.com`;`alisha13@adventure-works.com`;`arthur19@adventure-works.com`;... | ## Related content - [STRING_ESCAPE (Transact-SQL)](string-escape-transact-sql.md) - [STUFF (Transact-SQL)](stuff-transact-sql.md) - [CONCAT (Transact-SQL)](concat-transact-sql.md) - [CONCAT_WS (Transact-SQL)](concat-ws-transact-sql.md) - [Aggregate Functions (Transact-SQL)](aggregate-functions-transact-sql.md) - [String Functions (Transact-SQL)](string-functions-transact-sql.md)