STRING_AGG function

STRING_AGG function

Text data aggregation

Let’s consider the following task.

List the names of all Japanese vessels in the Ships table in a single line, separated by commas.

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;
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]

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;
ships_list
haruna,hiei,kirishima,kon,musashi,yamato

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;
countryships_list
gt.britainrenown,repulse,resolution,ramillies,revenge,royal oak,royal sovereign
japanharuna,hiei,kirishima,kongo,musashi,yamato
usaiowa,missouri,new jersey,wisconsin,north carolina,south dakota,washington,california,tennessee

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,'');
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]

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;
countryships_list
Gt.BritainRenown,Repulse,Resolution,Ramillies,Revenge,Royal Oak,Royal Sovereign
JapanMusashi,Yamato,Haruna,Hiei,Kirishima,Kongo
USANorth Carolina,South Dakota,Washington,Iowa,Missouri,New Jersey,Wisconsin,California,Tennessee

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;
countryships_list
Gt.BritainRamillies,Renown,Repulse,Resolution,Revenge,Royal Oak,Royal Sovereign
JapanHaruna,Hiei,Kirishima,Kongo,Musashi,Yamato
USACalifornia,Iowa,Missouri,New Jersey,North Carolina,South Dakota,Tennessee,Washington,Wisconsin

Suggested exercises: 139