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:
In MySQL, there is the remarkable GROUP_CONCAT aggregate function that provides the solution to this task:
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:
In SQL Server, our task can be solved in a less natural way – by retrieving the result set as XML:
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.
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: