Comma-Separated Course List Per Student — SQL

Solving tips

Collapsing many rows into one delimited string is `STRING_AGG(value, delimiter)` in PostgreSQL.
Sort the concatenation deterministically with `STRING_AGG(course, ', ' ORDER BY course)` — the ORDER BY goes inside the aggregate's parentheses.
GROUP BY the key you want one string per (student), and remember STRING_AGG skips NULLs silently.

Enrollments store one row per student-course pair. Produce a single alphabetized, comma-separated course list for each student.

Schema

CREATE TABLE enrollments (
  student  TEXT,
  course   TEXT
);

Sample data:

student	course
Alice	Physics
Alice	Math
Alice	Chemistry
Bob	Math
Bob	Biology

Task

Return one row per student with columns student and courses, where courses is that student’s courses joined into one string, sorted alphabetically and separated by ", " (comma and a space). Order by student ascending.

Expected output

student	courses
Alice	Chemistry, Math, Physics
Bob	Biology, Math

Approach

STRING_AGG concatenates the values within each group into a single delimited string. Grouping by student produces one row per student, and the inner ORDER BY course makes the concatenation order deterministic and alphabetical rather than dependent on physical row order.

Query

SELECT
  student,
  STRING_AGG(course, ', ' ORDER BY course) AS courses
FROM enrollments
GROUP BY student
ORDER BY student;

Walkthrough

The Alice group has three courses; sorted alphabetically they are Chemistry, Math, Physics, which STRING_AGG joins with ", " into Chemistry, Math, Physics. The Bob group sorts to Biology, Math. The outer ORDER BY student places Alice before Bob. Without the inner ORDER BY, the concatenation order would be arbitrary and could vary between runs.

Complexity & notes

One grouped pass with a per-group sort for the ordering, roughly O(n log n). STRING_AGG ignores NULL courses; to keep them you would COALESCE(course, '(none)') first. To drop duplicate courses use STRING_AGG(DISTINCT course, ', '), though DISTINCT and an ORDER BY on a different expression cannot be combined. Dialect note: PostgreSQL uses STRING_AGG; MySQL uses GROUP_CONCAT(course ORDER BY course SEPARATOR ', ') (watch its group_concat_max_len truncation limit), Oracle uses LISTAGG(course, ', ') WITHIN GROUP (ORDER BY course), and SQL Server uses STRING_AGG(course, ', ') WITHIN GROUP (ORDER BY course).