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;
[[ 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;
country | ships_list |
---|---|
gt.britain | renown,repulse,resolution,ramillies,revenge,royal oak,royal sovereign |
japan | haruna,hiei,kirishima,kongo,musashi,yamato |
usa | iowa,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,'');
[[ 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;
country | ships_list |
---|---|
Gt.Britain | Renown,Repulse,Resolution,Ramillies,Revenge,Royal Oak,Royal Sovereign |
Japan | Musashi,Yamato,Haruna,Hiei,Kirishima,Kongo |
USA | North 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;
country | ships_list |
---|---|
Gt.Britain | Ramillies,Renown,Repulse,Resolution,Revenge,Royal Oak,Royal Sovereign |
Japan | Haruna,Hiei,Kirishima,Kongo,Musashi,Yamato |
USA | California,Iowa,Missouri,New Jersey,North Carolina,South Dakota,Tennessee,Washington,Wisconsin |
Suggested exercises: 139