--- title: "STRING_AGG (Transact-SQL) | Microsoft Docs" ms.custom: "" ms.date: "04/19/2017" ms.prod: sql ms.prod_service: "database-engine, sql-database" ms.reviewer: "" ms.technology: t-sql ms.topic: conceptual f1_keywords: - "STRING_AGG" - "STRING_AGG_TSQL" helpviewer_keywords: - "STRING_AGG function" ms.assetid: 8860ef3f-142f-4cca-aa64-87a123e91206 author: MikeRayMSFT ms.author: mikeray monikerRange: "=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2017||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current" --- # STRING_AGG (Transact-SQL) [!INCLUDE[tsql-appliesto-ss2017-asdb-asdw-xxx-md](../../includes/tsql-appliesto-ss2017-asdb-asdw-xxx-md.md)] Concatenates the values of string expressions and places separator values between them. The separator is not added at the end of string. ![Topic link icon](../../database-engine/configure-windows/media/topic-link.gif "Topic link icon") [Transact-SQL Syntax Conventions](../../t-sql/language-elements/transact-sql-syntax-conventions-transact-sql.md) ## Syntax ``` STRING_AGG ( expression, separator ) [ ] ::= WITHIN GROUP ( ORDER BY [ ASC | DESC ] ) ``` ## Arguments *expression* Is 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* Is 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: ``` 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 `order_by_expression` is allowed per query. The default sort order is ascending. ## Return Types Return type is depends on first argument (expression). If input argument is string type (`NVARCHAR`, `VARCHAR`), result type will be same as 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 (Transact-SQL)](../../t-sql/functions/cast-and-convert-transact-sql.md). If the input expression is type `VARCHAR`, the separator cannot be type `NVARCHAR`. Null values are ignored and the corresponding separator is not added. To return a place holder for null values, use the `ISNULL` function as demonstrated in example B. `STRING_AGG` is available in any compatibility level. ## Examples ### 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 SELECT STRING_AGG (FirstName, CHAR(13)) AS csv FROM Person.Person; ``` [!INCLUDE[ssResult_md](../../includes/ssresult-md.md)] |csv | |--- | |Syed
Catherine
Kim
Kim
Kim
Hazem
... | `NULL` values found in `name` cells are not returned in result. > [!NOTE] > If using the Management Studio Query Editor, the **Results to Grid** option cannot implement the carriage return. Switch to **Results to Text** to see the result set properly. ### 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 SELECT STRING_AGG ( ISNULL(FirstName,'N/A'), ',') AS csv FROM Person.Person; ``` [!INCLUDE[ssResult_md](../../includes/ssresult-md.md)] |Csv | |--- | |John,N/A,Mike,Peter,N/A,N/A,Alice,Bob | ### C. Generate comma-separated values ```sql SELECT STRING_AGG(CONCAT(FirstName, ' ', LastName, ' (', ModifiedDate, ')'), CHAR(13)) AS names FROM Person.Person; ``` [!INCLUDE[ssResult_md](../../includes/ssresult-md.md)] |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 cannot implement the carriage return. Switch to **Results to Text** to see the result set properly. ### D. Return news articles with related tags Article and their tags are separated into different tables. Developer wants to return one row per each article with all associated tags. Using following query: ```sql SELECT a.articleId, title, STRING_AGG (tag, ',') as tags FROM dbo.Article AS a LEFT JOIN dbo.ArticleTag AS t ON a.ArticleId = t.ArticleId GROUP BY a.articleId, title; ``` [!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 towns: ```sql SELECT town, STRING_AGG (email, ';') AS emails FROM dbo.Employee GROUP BY town; ``` [!INCLUDE[ssResult_md](../../includes/ssresult-md.md)] |town |emails | |--- |--- | |Seattle |syed0@adventure-works.com;catherine0@adventure-works.com;kim2@adventure-works.com | |LA |sam1@adventure-works.com;hazem0@adventure-works.com | Emails returned in the emails column can be directly used to send emails to group of people working in some particular towns. ### F. Generate a sorted list of emails per towns Similar to previous example, the following query finds the email addresses of employees, groups them by town, and sorts the emails alphabetically: ```sql SELECT town, STRING_AGG (email, ';') WITHIN GROUP (ORDER BY email ASC) AS emails FROM dbo.Employee GROUP BY town; ``` [!INCLUDE[ssResult_md](../../includes/ssresult-md.md)] |town |emails | |--- |--- | |Seattle |catherine0@adventure-works.com;kim2@adventure-works.com;syed0@adventure-works.com | |LA |hazem0@adventure-works.com;sam1@adventure-works.com | ## See also [CONCAT (Transact-SQL)](../../t-sql/functions/concat-transact-sql.md) [CONCAT_WS (Transact-SQL)](../../t-sql/functions/concat-ws-transact-sql.md) [FORMATMESSAGE (Transact-SQL)](../../t-sql/functions/formatmessage-transact-sql.md) [QUOTENAME (Transact-SQL)](../../t-sql/functions/quotename-transact-sql.md) [REPLACE (Transact-SQL)](../../t-sql/functions/replace-transact-sql.md) [REVERSE (Transact-SQL)](../../t-sql/functions/reverse-transact-sql.md) [STRING_ESCAPE (Transact-SQL)](../../t-sql/functions/string-escape-transact-sql.md) [STUFF (Transact-SQL)](../../t-sql/functions/stuff-transact-sql.md) [TRANSLATE (Transact-SQL)](../../t-sql/functions/translate-transact-sql.md) [Aggregate Functions (Transact-SQL)](../../t-sql/functions/aggregate-functions-transact-sql.md) [String Functions (Transact-SQL)](../../t-sql/functions/string-functions-transact-sql.md)