Intro

I’ve got the following Order table:

I want to aggregate the data and group it by User it into the following table:

User	Articles
a@business.net	pen,notebook,stapler
b@business.net	pen,pen,notebook

MySql/MariaDB

Here the magic word GROUP_CONCAT will yeild the desired results.

SELECT [User], GROUP_CONCAT(Article SEPARATOR ',') AS Articles
FROM Order
GROUP BY [User];

Sometimes I want to aggregate in JSON, because most good SQL stacks have decent JSON parsing.

MySql/MariaDB has a thing called JSON_ARRAYAGG and works something like this:

SELECT [User], JSON_ARRAYAGG(Article) AS Articles
FROM Order
GROUP BY [User];

And it will produce this:

User	Articles
a@business.net	[“pen”,“paper”,“stapler”]
b@business.net	[“pen”,“pen”,“stapler”]

Neat!

MS Sql Server and Azure SQL use T-SQL and the magic words are slightly different.

STRING_AGG is the T_SQL equivalent of GROUP_CONCAT

SELECT [User], STRING_AGG(Article, ',') AS Articles
FROM Order
GROUP BY [User];

Unfortunately T-SQL still doesn’t provide a JSON alternative so I just do this:

SELECT [User], JSON_QUERY('[' + STRING_AGG('"' + Article + '"', ',') + ']') AS Articles
FROM Order
GROUP BY [User];