Aggregating data in SQL

Mar 06, 2023


I’ve got the following Order table:

OrderId User Article
1 pen
2 notebook
3 stapler
4 pen
6 pen
5 notebook

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

User Articles pen,notebook,stapler pen,pen,notebook


Here the magic word GROUP_CONCAT will yeild the desired results.

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

Json Aggregation

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 [“pen”,“paper”,“stapler”] [“pen”,“pen”,“stapler”]



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];

Json Aggregation

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];

Yasen Dinkov

7 levels of sessionless tokens

Syncing Feature Flags