In this lesson, you will learn how to flatten "one-to-many" relationships by collapsing multiple child records into a single, delimited string associated with a parent record.
The Challenge of One-to-Many
When working with relational databases, we often encounter a parent table (like Departments) and a child table (like Employees). A standard JOIN creates a Cartesian-like result where the parent record is duplicated for every child found. If you want a clean report showing exactly one row per department with a list of all employees in that department, standard grouping won't suffice. String aggregation allows you to "fold" those child rows into a single column.
Implementation: The STRING_AGG approach
While SQL syntax varies by engine, most modern systems use a variation of the STRING_AGG (or GROUP_CONCAT) function. This function takes a column and a delimiter (such as a comma) and combines the values for each group.
Imagine we have an Employees table and want to list all names per department:
SELECT
department_id,
STRING_AGG(employee_name, ', ') AS employee_list
FROM Employees
GROUP BY department_id;
Why this matters
Instead of receiving a result set with 50 rows for a department with 50 employees, this technique collapses that data into one concise row. The output would look like this:
| department_id | employee_list |
|---|---|
| 101 | "Alice, Bob, Charlie" |
| 102 | "David, Eve" |
This approach is highly effective for generating CSV exports, creating summary reports for dashboards, or preparing data for applications that expect a single, readable string rather than a list of individual rows. By controlling the delimiter, you ensure the output is immediately readable by both humans and external parsing tools.
Key Takeaway: String aggregation converts multiple child rows into a single, delimited string, allowing you to flatten hierarchical relationships into a clean, parent-level summary.