String aggregation with STRING_AGG in PostgreSQL - Step-by-Step Execution

Concept Flow - String aggregation with STRING_AGG

Start Query

↓

Group rows by key

↓

Collect strings per group

↓

Concatenate strings with separator

↓

Return aggregated string per group

↓

End Query

The query groups rows by a key, collects strings from each group, concatenates them with a separator, and returns the result.

Execution Sample

PostgreSQL

SELECT department, STRING_AGG(employee_name, ', ') AS employees
FROM staff
GROUP BY department;

This query groups employees by their department and combines their names into a single comma-separated string per department.

Execution Table

Step	Action	Group Key	Strings Collected	Concatenated Result
1	Start query execution	-	-	-
2	Group rows by department	Sales	Alice	Alice
3	Add next employee to Sales group	Sales	Alice, Bob	Alice, Bob
4	Group rows by department	HR	Carol	Carol
5	Add next employee to HR group	HR	Carol, Dave	Carol, Dave
6	Group rows by department	IT	Eve	Eve
7	Concatenate strings for Sales	Sales	Alice, Bob	Alice, Bob
8	Concatenate strings for HR	HR	Carol, Dave	Carol, Dave
9	Concatenate strings for IT	IT	Eve	Eve
10	Return final result set	-	-	-

💡 All groups processed and strings aggregated, query execution complete.

Variable Tracker

Variable	Start	After Step 3	After Step 5	After Step 9	Final
Sales group strings	empty	Alice, Bob	Alice, Bob	Alice, Bob	Alice, Bob
HR group strings	empty	empty	Carol, Dave	Carol, Dave	Carol, Dave
IT group strings	empty	empty	empty	Eve	Eve

Key Moments - 2 Insights

Why do we need to use GROUP BY with STRING_AGG?

STRING_AGG combines strings per group, so GROUP BY defines which rows belong together. Without GROUP BY, STRING_AGG would combine all rows into one string (see execution_table steps 2 and 4).

What happens if a group has only one string?

STRING_AGG returns that single string without adding the separator (see IT group in execution_table steps 6 and 9).

Visual Quiz - 3 Questions

Look at the execution_table, what is the concatenated result for the HR group after step 5?

A Carol

B Carol, Dave

C Dave

D Carol, Bob

💡 Hint

Check the 'Concatenated Result' column for HR group at step 5.

At which step does the Sales group first have two employees collected?

A Step 2

B Step 7

C Step 3

D Step 9

💡 Hint

Look at the 'Strings Collected' column for Sales group in execution_table.

If we remove GROUP BY, how would the final result change?

A All employee names would be combined into one string

B Each department would have its own aggregated string

C Query would return an error

D Only one employee name would be returned

💡 Hint

Recall that GROUP BY defines grouping; without it, STRING_AGG aggregates all rows together.

Concept Snapshot

STRING_AGG(expression, separator) aggregates strings from grouped rows.
Use with GROUP BY to combine values per group.
Returns concatenated string separated by given separator.
If group has one value, returns it as is.
Useful for listing items in one row per group.

Full Transcript

This visual execution traces how STRING_AGG works in PostgreSQL. The query groups rows by department and collects employee names per group. Then it concatenates these names with a comma and space separator. The execution table shows each step: grouping rows, collecting strings, and concatenating them. Variables track the strings collected per group as they grow. Key moments clarify why GROUP BY is needed and what happens with single-item groups. The quiz tests understanding of the aggregation process and effects of removing GROUP BY. The snapshot summarizes syntax and behavior for quick reference.