STRING_AGG function

Text data aggregation

Let’s consider the following task.

There is no problem getting the list of Japanese ships:

SELECT name 
FROM Ships s 
    JOIN Classes c ON s.class=c.class
WHERE country='Japan'
ORDER BY name;

In MySQL, there is the remarkable GROUP_CONCAT aggregate function that provides the solution to this task:

SELECT GROUP_CONCAT(name) ships_list 
FROM Ships s 
    JOIN Classes c ON s.class=c.class
WHERE country='Japan'
ORDER BY name;

By default, the comma is used as a list separator; however, we can choose any symbol.

Grouping makes it easy to obtain a ship list for each country:

SELECT country, GROUP_CONCAT(name) ships_list
FROM Ships s 
    JOIN Classes c ON s.class=c.class
GROUP BY country
ORDER BY country, name;

In SQL Server, our task can be solved in a less natural way – by retrieving the result set as XML:

SELECT STUFF(
    (SELECT ','+name AS 'data()' 
     FROM Ships s 
         JOIN Classes c ON s.class=c.class
     WHERE country='Japan'
     ORDER BY name FOR XML PATH('')
    ),1,1,'');

Grouping by country will make the query even more complex. Thus, we won’t even attempt to do that, since in SQL Server, beginning with version 2017, there is a function called STRING_AGG that allows concatenating strings. It takes two mandatory arguments – the string expression to be concatenated, and the string separator.

SELECT country, STRING_AGG(name,',') ships_list
FROM Ships s 
    JOIN Classes c ON s.class=c.class
GROUP BY country
ORDER BY country;

As it can be seen the list of ship names isn’t ordered. GROUP_CONCAT-like sorting doesn’t work here. This can be fixed by using the optional WITHIN GROUP clause:

SELECT country, STRING_AGG(name,',') WITHIN GROUP (ORDER BY name) ships_list
FROM Ships s 
    JOIN Classes c ON s.class=c.class
GROUP BY country
ORDER BY country;

Suggested exercises: 139