{"id":55244,"date":"2020-01-03T12:33:34","date_gmt":"2020-01-03T12:33:34","guid":{"rendered":"https:\/\/www.sqlshack.com\/?p=55244"},"modified":"2020-01-06T12:17:34","modified_gmt":"2020-01-06T12:17:34","slug":"string_agg-function-in-sql","status":"publish","type":"post","link":"https:\/\/www.sqlshack.com\/string_agg-function-in-sql\/","title":{"rendered":"An overview of the STRING_AGG function in SQL"},"content":{"rendered":"

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

<\/p>\n

How it works<\/h2>\n

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

\n \"The

STRING_AGG function first example<\/h3>\n

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

DROP TABLE IF EXISTS [PersonTestTable]\r\nGO\r\nCREATE TABLE [PersonTestTable](\r\n    [FirstName] [varchar](400) NULL,\r\n    [LastName] [varchar](400) NULL,\r\n    [Mail] [varchar](100) NULL,\r\n    Country [varchar](100) NULL,\r\n    Age [int] NULL\r\n    \r\n) ON [PRIMARY]\r\nGO\r\n    \r\nINSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Lawrence',N'Williams',N'uhynb.ndlguey@vtq.org',N'U.S.A.',21)\r\nINSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Gilbert',N'Miller',N'loiysr.jeoni@wptho.co',N'U.S.A.',53)\r\nINSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Salvador',N'Rodriguez',N'tjybsrvg.rswed@uan.org',N'Russia',46)\r\nINSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Ernest',N'Jones',N'psxkrzf.jgcmc@pfdknl.org',N'U.S.A.',48)\r\nINSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Jerome',N'Garcia',NULL,N'Russia',46)\r\nINSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Roland',N'Smith','xpdek.qpl@kpl.com',N'U.S.A. ',35)\r\nINSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Stella',N'Johnson',N'qllyoxgr.jsntdty@pzwm.org',N'Russia',24)\r\nINSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Aria',N'Anderson',N'sjgnz.voyyc@cvjg.com',N'Brazil ',25)\r\nINSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Edward',N'Martinez','pokjs.oas@mex.com',N'Mexico ',27)\r\nINSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Nicholas',N'Brown',N'wpfiki.hembt@uww.co',N'Russia ',43)\r\nINSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Ray',N'Wilson',NULL,N'Russia',41)\r\nINSERT INTO [dbo].[PersonTestTable]([FirstName],[LastName],[Mail],[Country],[Age]) VALUES (N'Jorge',N'Davis',N'bhlji.zwngl@kle.com',N'Russia ',49)\r\nGO<\/pre>\n
\n    In this first example, the STRING_AGG function will take all rows expression from the FirstName<\/strong>
\n    column of the PersonTestTable<\/strong> table and then generate a concatenated string with these rows
\n    expression. At the same time, the concatenated expressions will be separated with the hyphen (–<\/strong>)
\n    sign:\n<\/p>\n
SELECT FirstName FROM PersonTestTable\r\nSELECT STRING_AGG(FirstName,'-') AS Result FROM PersonTestTable<\/pre>\n
\n    \"STRING_AGG
Syntax of STRING_AGG function in SQL<\/h2>\n
\n    The syntax of the STRING_AGG function looks like below:\n<\/p>\n
STRING_AGG ( expression, separator ) [ <order_clause> ]<\/pre>\n
\n    The expression<\/strong> parameter specifies any expressions that will be concatenated. The
\n    separator<\/strong> parameter is used to separate expressions that will be concatenated. The
\n    order_clause<\/strong> parameter is an optional parameter and helps to order the concatenated expression and
\n    it must be used with WITHIN GROUP statement.\n<\/p>\n
Sorting result of STRING_AGG function in SQL<\/h2>\n
\n    STRING_AGG function allows sorting concatenated expressions in descending or ascending order. In this example, we
\n    will sort the concatenated expressions according to the FirstName<\/strong> column rows expression with the
\n    WITHIN GROUP<\/strong> statement:\n<\/p>\n
SELECT FirstName FROM [PersonTestTable] ORDER BY FirstName ASC\r\nGO\r\nSELECT STRING_AGG(FirstName,'-')  WITHIN GROUP ( ORDER BY FirstName ASC)  AS Result FROM [PersonTestTable]<\/pre>\n<\/p>\n
\n    \"STRING_AGG
\n    As we can see clearly the STRING_AGG function sorted the concatenated expressions in the ascending order according to row values of the FirstName <\/strong>column. We need to underline one point about this type of usages.
\n    The <\/strong>GROUP BY clause will be necessary if the STRING_AGG result is not a sole column in the result
\n    set of the query. In the next section, we will learn this concept.\n<\/p>\n
How to group concatenated expressions with STRING_AGG<\/h2>\n
\n    GROUP BY clause provides grouping the rows that have the same values in SQL Server. In the following example, we
\n    will generate grouped and concatenated e-mail addresses by the Country<\/strong> column:\n<\/p>\n
SELECT Country,STRING_AGG(Mail,',')  WITHIN GROUP ( ORDER BY FirstName ASC)  AS\r\nResult FROM PersonTestTable\r\nGROUP BY Country\r\nORDER BY Country asc<\/pre>\n<\/p>\n
\n    \"Grouping
\n    At this point, we have to take account of one consideration about the STRING_AGG function. The NULL values are
\n    ignored when the STRING_AGG concatenates the expressions in the rows and it also does not add an extra separator
\n    between the expressions due to NULL values. The following example will be illustrated in this case:\n<\/p>\n
SELECT Country,Mail\r\nResult FROM [PersonTestTable]\r\nwhere country='Russia' \r\ngroup by Country, Mail\r\n    \r\nSELECT Country,\r\nSTRING_AGG(Mail,',')  WITHIN GROUP ( ORDER BY Mail ASC)  AS\r\nResult FROM [PersonTestTable]\r\nwhere country='Russia' \r\ngroup by Country<\/pre>\n<\/p>\n
\n    \"NULL
\n    As we can see, the NULL value did not affect the result of the function.\n<\/p>\n
The old method that can be used instead of STRING_AGG function<\/h2>\n
\n    If we are using an older version than SQL Server 2017, we can use FOR<\/strong> XML PATH<\/strong> and
\n    STUFF<\/strong> statements combinations in the query to concatenate rows expressions. However, this method is
\n    more complicated than STRING_AGG function but it can be useful for the older version of the SQL Server. The
\n    following query returns the same result as the previous sample:\n<\/p>\n
SELECT STUFF((SELECT '-' + FirstName as [text()] FROM PersonTestTable FOR XML PATH('')),1,1,'') AS Result<\/pre>\n<\/p>\n
\n    \"STUFF
FOR XML PATH <\/strong>statement provides to generate an XML<\/strong> element from the query
\n    result. <\/strong>When we execute the following query, it transforms query result to XML:\n<\/p>\n
SELECT '-' + FirstName as [text()] FROM PersonTestTable FOR XML PATH<\/pre>\n<\/p>\n
\n    \"FOR
\n    If we click the result of the query, we can find out the XML more clearly:\n<\/p>\n
\n    \"Output
\n    When we add the blank string option at the end of the FOR XML PATH, we will obtain concatenated and separated
\n    expressions:\n<\/p>\n
SELECT '-' + FirstName as [text()] FROM PersonTestTable FOR XML PATH('')<\/pre>\n<\/p>\n
\n    \"FOR
STUFF<\/strong><\/a> function helps to delete a
\n    specified part of the string and then it can add a new string to it. Finally, we will clear the first extra
\n    separator with the STUFF function.\n<\/p>\n
How to generate a concatenated rows in a single cell <\/h2>\n
\n    The carriage return allows setting the cursor to the beginning of the next line. We can provide this option in SQL
\n    with CHAR(13)<\/strong> statement. We will use CHAR(13) statement as a separator parameter for STRING_AGG
\n    function so that we can generate concatenated rows into a single cell:\n<\/p>\n
SELECT STRING_AGG(FirstName,CHAR(13)) AS Result FROM [PersonTestTable]<\/pre>\n<\/p>\n
\n    \"Generating
\n    As we can see, we did not obtain the result that we wished in the SQL Server Management Studio result tab. In fact,
\n    it misleads us in SSMS because of the query result option. We will change this option in SSMS so that we achieve the
\n    proper visual. We will find the Options<\/strong> setting under the Tools<\/strong> menu and then
\n    change the Default destination for result <\/strong>option in the Query Results<\/strong> setting. We
\n    will change the Results to grids<\/strong> option to Result to text<\/strong>:\n<\/p>\n
\n    \"Change
\n    After this option changing, SSMS shows the result in the text. We will re-execute the same query in a new query
\n    window:\n<\/p>\n
SELECT STRING_AGG(FirstName,CHAR(13)) AS Result FROM PersonTestTable<\/pre>\n<\/p>\n
\n    \"Generating
How to remove duplicate values in STRING_AGG function<\/h2>\n
\n    In some cases, we may need to eliminate duplicate values from the concatenated result of the STRING_AGG function. To
\n    handle this issue, we can use a two-tier query. In the first SELECT<\/strong> statement, we will eliminate
\n    the duplicate rows and then obtain unique values. Then, we will concatenate the unique expression with the
\n    STRING_AGG function:\n<\/p>\n
SELECT STRING_AGG(Cnty, '-')  FROM\r\n(\r\n (SELECT DISTINCT Country AS [Cnty] FROM PersonTestTable)\r\n) AS TMP_TBL<\/pre>\n<\/p>\n
\n    \"Eliminating
Advanced details about STRING_AGG function in SQL<\/h2>\n
\n    The result type of STRING_AGG is determined according to the first expression that is taken by the function. There
\n    is no doubt that the nvarchar<\/strong> and varchar<\/strong> types concatenated results will be in
\n    the same type. However, if we concatenate other datatypes which can be converted into string datatypes (int, float,
\n    datetime and etc.). The result data types will be NVARCHAR(4000) for non-string data types. In the following
\n    example, we will create a TempTableForFunction<\/strong> table that has only a float data type column. We
\n    will try to combine these table expressions through the STRING_AGG function and then the function result will create
\n    the TempTableForFunctionResult<\/strong> table. TempTableForFunctionResult <\/strong>column will be
\n    generated according to STRING_AGG function result datatype:\n<\/p>\n
DROP TABLE IF EXISTS TempTableForFunction\r\nDROP TABLE IF EXISTS TempTableForFunctionResult\r\nGO\r\n    \r\nCREATE TABLE TempTableForFunction\r\n(SampleVal Float)\r\n    \r\nINSERT INTO TempTableForFunction VALUES (12.67) , (98.09),(65.42),(56.72),(129.12)\r\n    \r\nSELECT STRING_AGG(SampleVal,'-') WITHIN GROUP ( ORDER BY SampleVal ASC) AS Result INTO  TempTableForFunctionResult   FROM TempTableForFunction  \r\n    \r\nSELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TempTableForFunctionResult'\r\n    \r\nSELECT STRING_AGG(SampleVal,'-') WITHIN GROUP ( ORDER BY SampleVal ASC) AS Result   FROM TempTableForFunction<\/pre>\n
\n    Now, examine the result:\n<\/p>\n
\n    \"Analyzing
\n    The following table shows the STRING_AGG function result types against expression data types:\n<\/p>\n\n\n\n\n\n\n\n
\n
Input expression type<\/strong>\n            <\/p>\n<\/td>\n
\n
STRING_AGG function result type<\/strong>\n            <\/p>\n<\/td>\n<\/tr>\n
\n
\n                NVARCHAR(MAX)\n            <\/p>\n<\/td>\n
\n
\n                NVARCHAR(MAX)\n            <\/p>\n<\/td>\n<\/tr>\n
\n
\n                VARCHAR(MAX)\n            <\/p>\n<\/td>\n
\n
\n                VARCHAR(MAX)\n            <\/p>\n<\/td>\n<\/tr>\n
\n
\n                NVARCHAR(1…4000)\n            <\/p>\n<\/td>\n
\n
\n                NVARCHAR(4000)\n            <\/p>\n<\/td>\n<\/tr>\n
\n
\n                VARCHAR(1…8000)\n            <\/p>\n<\/td>\n
\n
\n                VARCHAR(8000)\n            <\/p>\n<\/td>\n<\/tr>\n
\n            
int, bigint, smallint, tinyint, numeric, float, real, bit, decimal,<\/br>
\n            smallmoney, money, datetime, datetime2,<\/p>\n<\/td>\n		\n
\n                NVARCHAR(4000)\n            <\/p>\n<\/td>\n<\/tr>\n<\/table>\n
\n    At this point, we have to take account of one issue about the STRING_AGG function, it sorted the numbers properly.
\n    If we look behind at the scene of the query, it means that we will analyze the execution plan of the following query
\n    with ApexSQL Plan<\/a>:\n<\/p>\n
SELECT STRING_AGG(SampleVal,'-') WITHIN GROUP ( ORDER BY SampleVal ASC) AS Result FROM TempTableForFunction<\/pre>\n<\/p>\n
\n    \"STRING_AGG
\n    The sort operation is processed before the Stream Aggregate operation so the numbers are sorted out properly. On the
\n    other hand, there is a warning sign shown over the SELECT image. If we hover over this image, we can find out more
\n    details about this issue:\n<\/p>\n
\n    \"Implicit
\n    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<\/a> article
\n    to learn more details about the implicit conversion notion.\n<\/p>\n
Conclusion<\/h2>\n
\n    In this article, we explored the STRING_AGG function in SQL and completed various examples of this function.
\n    STRING_AGG is a very useful and simple function to convert rows expression into a single string. On the other hand,
\n    we can use the older version methods to solve these types of issues.\n<\/p>\n
<\/div>\n