loading..
   English
12:40

STRING_AGG function

Text data aggregation

Lets 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:

Console
Execute
  1. SELECT name FROM Ships s JOIN Classes c ON s.class=c.class
  2. WHERE country='Japan'
  3. ORDER BY name;

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

  1. SELECT GROUP_CONCAT(name) ships_list FROM Ships s JOIN Classes c ON s.class=c.class
  2. WHERE country='Japan'
  3. 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:

  1. SELECT country, GROUP_CONCAT(name) ships_list
  2. FROM Ships s JOIN Classes c ON s.class=c.class
  3. GROUP BY country
  4. 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:

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

Grouping by country will make the query even more complex. Thus, we wont 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.

  1. SELECT country, STRING_AGG(name,',') ships_list
  2. FROM Ships s JOIN Classes c ON s.class=c.class
  3. GROUP BY country
  4. 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 isnt ordered. GROUP_CONCAT-like sorting doesnt work here. This can be fixed by using the optional WITHIN GROUP clause:

  1. SELECT country, STRING_AGG(name,',') WITHIN GROUP (ORDER BY name) ships_list
  2. FROM Ships s JOIN Classes c ON s.class=c.class
  3. GROUP BY country
  4. 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

Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100