How To Use STRING_AGG – Concat Multiple Row Values In SQL Server

Concat multiple row values in SQL Server is a well-known familiar problem. Every database developer happened to write concatenation string queries for reporting or general purpose. Numerous methods including XML PATH, COALESCE function, Recursive CTE been used to achieve desired results.

Addition of STRING_AGG function in SQL Server 2017 has made concatenate multiple row values in SQL Server a piece of cake for developers. We can kick out old methods of writing long wrapper codes to implement this and use a saviour STRING_AGG function to write concise code.

STRING_AGG – Concatenate input string values together by placing a delimiter between them.

Syntax

STRING_AGG ( string, delimiter)

Arguments

String - List of individual string values to be concatenated.
Delimiter - Single/Multiple characters value act as a separator between concatenated string values.

We’re creating table dbo.Players to demonstrate string concatenation in SQL Server. It has Name column which has repeated player names along with their trophy names. We’re requested to concatenate trophy names and generate a comma delimited list against each distinct player name as in following.

USE tempdb;

DROP TABLE IF EXISTS dbo.Player;

CREATE TABLE dbo.Player
(
    Name    VARCHAR(255)
   ,Trophy    VARCHAR(255)
);

INSERT INTO dbo.Player VALUES ('Federer', 'Wells')
                             ,('Federer', 'Miami')
                             ,('Federer', 'Halle')
                             ,('Nadal','Madrid')
                             ,('Nadal', 'Italian')
                             ,('Djokovic', 'Paris');
    
SELECT *
FROM   dbo.Player;

Before SQL Server 2017 – Concat Multiple Row values In SQL Server

We’ve been using XML PATH to concat multiple row values since SQL Server 2005. This method has worked ideally in different scenarios handling special characters as well.

XML PATH generates a string which outputs it as XML elements first. With the help of STUFF function, we remove the leading comma and extra space character. Adding grouping and order by to it we get distinct values and delimited string values.

SELECT Name, Trophies = STUFF((SELECT ', ' + Trophy
                               FROM  dbo.Player As P1
                               WHERE P1.Name = P2.Name
                               ORDER BY P1.Trophy
                               FOR XML PATH(''), TYPE).value('.[1]', 'varchar(max)'), 1, 2, '')
FROM dbo.Player as P2
GROUP BY P2.Name;

SQL Server 2017 – Concat Multiple Row values In SQL Server

STRING_AGG function was added to SQL Server 2017 edition. Finally, Microsoft has come up with a simplified approach to concat multiple row values in SQL server. The STRING_AGG function makes writing queries much easier. Similar to LISTAGG function in Oracle.

Let’s demonstrate STRING_AGG function with a few helpful examples here.

1. STRING_AGG – Create a Single Row Comma Delimited List

Concat multiple row values in a single column table. Understandably simple method, we can use a STRING_AGG function with a delimiter ‘, ‘

Note the added space after a comma.

DECLARE @Player TABLE
(
    Name VARCHAR(255)
);

INSERT INTO @Player VALUES
('Federer')
,('Nadal')
,('Djokovic')
,('Murry');

SELECT STRING_AGG(Name, ', ') as Single_Delimited_List
FROM   @Player;

2. STRING_AGG – Handle NULLs In Concatenation

Default functionality of STRING_AGG function ignores the NULL value and produces output by concatenating NOT NULL values. We can replace NULL values by user-defined text to include them in concatenated string like the following example.

DECLARE @Player TABLE
(
   Name VARCHAR(255)
);

INSERT INTO @Player VALUES
('Federer')
,('Nadal')
,('Djokovic')
,(NULL)
,('Murry');

SELECT STRING_AGG(ISNULL(Name,'N/A'), ', ') as Single_Delimited_List_Nulls
FROM   @Player;

3. STRING_AGG – Concat Multiple Row Values Using Table Join & Groping

Using STRING_AGG function along with joining two tables and groping based on id to select distinct player names and comma delimited list.

DECLARE @Player TABLE
(
   Id   INT
  ,Name VARCHAR(255)
);

INSERT INTO @Player VALUES
 (1,'Federer')
,(2,'Nadal')
,(3,'Djokovic');

DECLARE @Trophy TABLE
(
    PlayerId INT
   ,Name VARCHAR(255)
);

INSERT INTO @Trophy VALUES
 (1, 'Wells')
,(1, 'Madrid')
,(2, 'Italian')
,(2, 'Canadian')
,(2, 'Cincinnati')
,(3, 'Shanghai')
,(3, 'Paris');

SELECT P.Id, STRING_AGG(T.Name, ', ') as TrophyNames
FROM   @Player as P INNER JOIN @Trophy as T ON P.Id = T.PlayerId
GROUP BY P.Id;

4. STRING_AGG – Concat Multiple Row Values Using Groping & Ordering

Paying close attention to the above example we can see that list of trophy names are not in alphabetical order. STRING_AGG function has provided additional option WITHIN GROUP to sort the values and concatenate them.

DECLARE @Player TABLE
(
   Id   INT
  ,Name VARCHAR(255)
);

INSERT INTO @Player VALUES
 (1,'Federer')
,(2,'Nadal')
,(3,'Djokovic');

DECLARE @Trophy TABLE
(
    PlayerId INT
   ,Name VARCHAR(255)
);

INSERT INTO @Trophy VALUES
 (1, 'Wells')
,(1, 'Madrid')
,(2, 'Italian')
,(2, 'Canadian')
,(2, 'Cincinnati')
,(3, 'Shanghai')
,(3, 'Paris');

SELECT P.Id, STRING_AGG(T.Name, ', ') WITHIN GROUP (ORDER BY T.Name ASC) as TrophyNames
FROM   @Player as P INNER JOIN @Trophy as T ON P.Id = T.PlayerId
GROUP BY P.Id;

Summary

STRING_AGG function simplifies concatenation of multiple row values in SQL Server compared to XML PATH method. It can save a lot of time dealing with special characters and data conversion issues faced earlier.

If you like this post you may read SQL Server posts published on Tech-recipes.