loading..
Ðóññêèé    English
22:28

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:

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 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.

  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 isn’t ordered. GROUP_CONCAT-like sorting doesn’t 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

Suggested exercises: 139

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 DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
Êàê èçãîòîâèòü êðàñêîïóëüò ñâîèìè ðóêàìè èç ïûëåñîñà?
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.