New Built-in Function STRING_AGG() – SQL Server 2017

SQL Server 2017 introduces a set of useful functions like STRING_AGG(), STRING_SPLIT(), TRIM()… and many more. In this tip, I am going to discuss one of the built-in aggregate functions – “STRING_AGG()”.

The function concatenates values from rows as one value with a separator, and the main advantage is it doesn’t add the separator at the end of the value. It means we do not have to use the old XML trick to concatenate values. 

Syntax for the function

STRING_AGG ( expression, separator ) 
    WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )

Demonstration

Let’s follow the demonstration to understand how you can use it.

  • Create a simple table structure:
CREATE TABLE sel
       (
             Id INT,
             Customer VARCHAR(100),
             InsertDT datetime
       )
       GO
  • Insert some random data into the table
INSERT INTO sel VALUES (00012, 'DELL',CURRENT_TIMESTAMP)
INSERT INTO sel VALUES (00012, 'INKP',CURRENT_TIMESTAMP)
INSERT INTO sel VALUES (00013, 'KOMT',CURRENT_TIMESTAMP)
INSERT INTO sel VALUES (00014, 'IHIS',CURRENT_TIMESTAMP)
INSERT INTO sel VALUES (00015, 'HIST',CURRENT_TIMESTAMP)
INSERT INTO sel VALUES (00013, 'KPMG',CURRENT_TIMESTAMP)
INSERT INTO sel VALUES (00012, 'ROSE',CURRENT_TIMESTAMP)
  • Let’s apply the STRING_AGG() function to get the comma separated values under a single row;
SELECT Id,
       STRING_AGG (Customer, ',') WITHIN GROUP (ORDER BY Id ASC) Names,
       MAX(InsertDT) AS InsertDT
FROM sel
GROUP BY Id;

Here is output of the above query

Id          Names                                         InsertDT
--------------------------------------------------------------------------------
12          DELL,INKP,ROSE                                2018-02-22 07:13:03.563

13          KPMG,KOMT                                     2018-02-22 07:13:03.563

14          IHIS                                          2018-02-22 07:13:03.563

15          HIST                                          2018-02-22 07:13:03.563

(4 row(s) affected)

 To check more about STRING_AGG(),  kindly follow the MSDN link.

Hope, you find the post useful!

2 thoughts on “New Built-in Function STRING_AGG() – SQL Server 2017

  • riad

    Hi Dharmendra,

    thank you very much for your add its exactly what i needed but since I m a beginner in SQL I have one question for you. Do you now exactly which version of sql server manager studio works with this function. Because I just downloaded the last one on Microsoft but It didn’t work I obtained this message ‘STRING_AGG’ is not a recognized built-in function name. Could you please tell me which version of SQL server should I download please.

    Thank you for your help in advance

    Reply
    • Dharmendra

      Thanks Riad for your feedback! It is part of SQL Server 2017 engine feature NOT Management studio feature. So, you have to have SQL Server 2017 to use this function.

      Reply

Leave a comment

Your email address will not be published. Required fields are marked *