20:03

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

New sql features

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

SELECT name FROM Ships s JOIN Classes c ON s.class=c.class
WHERE country='Japan'
ORDER BY name;

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:

Console

Execute

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,'');

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

MySQL GROUP_CONCAT SQL Server 2017 STRING_AGG WITHIN_GROUP VARCHAR(MAX)

Previous [EOMONTH function]

[STRING_SPLIT function] Next

Last added:

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 Problems and solutions

S. I. Moiseenko

New sql features

STRING_AGG function

Text data aggregation