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!