Employee Roster per Department — STRING_AGG and ARRAY

The problem

Helix Systems' HR team is building an employee roster for each department that shows both name and job title together.

Write a query to return every department ID and a semicolon-separated list of employee entries, where each entry is the employee's name followed by - and their title. Entries should be arranged alphabetically by employee name within each list.

Assumptions:

The employees table has one row per employee with a department_id, a name, and a title.
Each department_id with at least one employee should appear once.
For each department, the roster contains one entry per employee in the form <name> - <title>. Entries are separated by '; ' and arranged alphabetically by name.

Output:

One row per department, with columns department_id and employee_roster.

Schema · hr 4 tables

departments

id integer

name text

location text

budget numeric

salaries

id integer

employee_id integer

amount numeric

effective_date date

end_date? date

employees

id integer

name text

email text

department_id integer

manager_id? integer

hire_date date

title text

is_active boolean

job_history

id integer

employee_id integer

title text

department_id integer

start_date date

end_date? date

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  department_id,
  STRING_AGG(
    CONCAT_WS(' - ', name, title),
    '; '
    ORDER BY
      name
  ) AS employee_roster
FROM
  employees
GROUP BY
  department_id

The shape

The roster is built in two layers. CONCAT_WS(' - ', name, title) runs per row and produces strings like 'Sarah Chen - CEO' for each employee. STRING_AGG then runs per department, joining those per-row strings with '; ' between them and ordering them alphabetically by employee name. Per-row construction wrapped inside per-group aggregation.

Clause by clause

SELECT department_id, STRING_AGG(CONCAT_WS(' - ', name, title), '; ' ORDER BY name) AS employee_roster returns the department and its assembled roster. CONCAT_WS (concatenate with separator) takes the separator as its first argument and any number of values after it, joining the values with the separator between them. Each employee row produces a single string. STRING_AGG then collects those strings into the per-department list, using '; ' as the between-entries separator and ordering the entries by the employee's name column. The ORDER BY name controls the sequence inside each department's value.
FROM employees reads the employee rows. Every employee with a department contributes.
GROUP BY department_id partitions the rows by department so the aggregate runs once per department. One output row per distinct department_id.

Why this and not `name || ' - ' || title`

The || operator would also concatenate the two columns and the literal, and on this data it produces the same string. The difference shows up the moment any of those columns goes NULL. With ||, any NULL operand turns the whole expression into NULL, and the entire employee entry disappears. With CONCAT_WS, NULL inputs are silently skipped: a NULL title would yield 'Sarah Chen' rather than wiping the row out. The prompt has every title populated, but reaching for CONCAT_WS makes the query safe against the data drift that would silently turn rosters into NULL.

You practiced STRING_AGG(CONCAT_WS(' - ', col1, col2), separator ORDER BY ...) — combine a per-row construction with a per-group aggregation, two layers of string assembly in one expression.

Return every department ID and a semicolon-separated list of employee entries, where each entry is the employee's `name` followed by ` - ` and their `title`. Entries should be arranged alphabetically by employee name within each list

The shape

Clause by clause

Why this and not `name || ' - ' || title`

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

The shape

Clause by clause

Why this and not name || ' - ' || title

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

Why this and not `name || ' - ' || title`