Employee Names per Department — STRING_AGG and ARRAY

The problem

Helix Systems' HR team needs a per-department roster showing every employee's name as a single combined text value.

Write a query to return every department ID and a comma-separated list of employee names, with the names arranged alphabetically within each list.

Assumptions:

The employees table has one row per employee with a department_id and a name.
Each department_id with at least one employee should appear once.
For each department, the names list contains every name value of employees in that department, separated by ', ' and arranged alphabetically.

Output:

One row per department, with columns department_id and employee_names.

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(
    name,
    ', '
    ORDER BY
      name
  ) AS employee_names
FROM
  employees
GROUP BY
  department_id

The shape

STRING_AGG(name, ', ' ORDER BY name) joins every employee's name in a department into a single comma-separated text value, with names arranged alphabetically inside each list. HR gets one row per department carrying the full roster.

Clause by clause

SELECT department_id, STRING_AGG(name, ', ' ORDER BY name) AS employee_names returns the department's ID and its concatenated roster. The first argument is the column to collect; the second is the literal ', ' placed between adjacent names. The ORDER BY name inside the aggregate is what produces 'Alex Bell, Anna Kim, Ben Walsh, ...' in true alphabetical order rather than the order the rows happened to sit in the table.
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, which matches the per-department roster the output spec asks for.

Why this and not `COUNT(name)`

COUNT would collapse each department to a single number — useful for headcount, but it throws every name away. STRING_AGG runs the same grouping but preserves the individual values, returning the contributing rows packaged as a delimited string.

You practiced STRING_AGG over a text column — the same shape as numeric aggregates, but the result is a delimited string carrying every contributing value in order.

Return every department ID and a comma-separated list of employee names, with the names arranged alphabetically within each list

The shape

Clause by clause

Why this and not `COUNT(name)`

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 COUNT(name)

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

Why this and not `COUNT(name)`