String Aggregation

String aggregation is not very useful, but is kinda cool. It is a way of taking multiple records and concatenating (combining) them together into a string value. That's cool because you can put more than one record in a single column. This allows you provide multiple values without multiple rows of data. But, depending on what you are doing, it probably will not be use often.

The Example Setup

Let me give you an example. Suppose you have a table listing students called person. There is one record per student.

person_idx	person_id	person_first	person_last
1	X0001	Abigail	Addams
2	X0002	Bobby	Bail
3	X0003	Charlie	Carson
4	X0004	Doug	Davis

There is also a table with the student high school history called hschool.

hschool_idx	hschool_id	hschool_grad_date
1	X0001
2	X0002	01/10/2024
3	X0003	02/12/2020
4	X0003	02/22/2025
5	X0004	03/13/2023
6	X0004

Note that some of the students have more than one graduation date. This is not a far-fetched hypothetical. Students can graduate and then finish a certificate program. Or they can be home schooled and never have a graduation date. Or... well there are plenty of scenarios.

So how do you join the graduation dates to the person table while retaining only one row per student? One option is to create a string aggregation grouping.

String Aggregating The Table

To start, lets get the data from the hschool table in a string aggreggated format. We use the following SQL.

SELECT
	hschool_id,
	STRING_AGG(hschool_grad_date, ', ') AS "grad_dates"
FROM hschool
GROUP BY hschool_id;

hschool_id	grad_dates
X0001
X0002	01/01/2025
X0003	01/01/2025, 02/02/2020
X0004	03/03/2023

The string_agg() function has grouped the graduation dates into a single string in a single column. The students have only one row per student.

A Left Join

Now we can query the person data and do a left join to add the graduation dates like this:

SELECT
	p.person_id,
	p.person_first,
	p.person_last,
	hs.grad_dates
FROM person AS p
LEFT JOIN (
	SELECT
		hschool_id,
		STRING_AGG(hschool_grad_date, ', ') AS grad_dates
	FROM hschool
	GROUP BY hschool_id
	) AS hs ON hs.hschool_id = p.person_id

person_id	person_first	person_last	grad_dates
X0001	Abigail	Addams
X0002	Bobby	Bail	01/01/2025
X0003	Charlie	Carson	01/01/2025, 02/02/2020
X0004	Doug	Davis	03/03/2023,

Note, the fourth student (X0004) has two dates that have been concatenated together but one of them is a zero length string. Because of this the student has a stray comma at the end of the grad dates string.

Joining As A Column Definition

Alternatively, you can add the grad_dates to the column definitions in the central select statement.

SELECT
	person_id,
	person_first,
	person_last,
	(SELECT STRING_AGG(hschool_grad_date, ', ')
		FROM hschool WHERE hschool_id = person_id
	) AS grad_dates
FROM person

With this format we don't even need the group by clause. For each person record we are selecting only the hschool records where the id values match.

Gettin' Freaky With The Formatting

The second argument to the string_agg() is the character string to insert in-between each value being aggregated. In the above examples we used a comma and a space. But this can cause problems with data imports. If the data is saved as a comma separated values (CSV) file each column is separated by a comma. This can lead to errors if the data is imported into a table or a spreadsheet.

But we can supply STRING_AGG() with almost any characters as a separator. One common format is to use pipe characters ("|") between values. You should probably use a space, pipe, space combination such as STRING_AGG(hschool_grad_date, ' | '). This would result in the following data.

person_id	person_first	person_last	grad_dates
X0001	Abigail	Addams
X0002	Bobby	Bail	01/01/2025
X0003	Charlie	Carson	01/01/2025 | 02/02/2020
X0004	Doug	Davis	03/03/2023 |

If we want to get creative we could use a something more exotic like STRING_AGG(hschool_grad_date, ' <[|&|]> ') which would result in:

person_id	person_first	person_last	grad_dates
X0001	Abigail	Addams
X0002	Bobby	Bail	01/01/2025
X0003	Charlie	Carson	01/01/2025 <[|&|]> 02/02/2020
X0004	Doug	Davis	03/03/2023 <[|&|]>

Go nuts! The worst that can happen is your end users will despise you.