How do I join table data into one comma separated column

Question

I have two tables with one-to-many relationships: employees and companies they worked for. Something like:

Table "Employee"

ID | Name 
=============
1  | Mike 
2  | Diana
3  | Emily

Table "Positions"

ID | Position | EmployeeID | StartDate | FinishDate | ...
=============
1  | Janitor | 1 | .... 
2  | Dustman | 1 | .... 
3  | Dishwasher | 2 | ...

How do I write an SQL query that will tell me that Mike was Janitor and Dustman, and Diana was a dishwasher? Each information should be presented in a single row. Something like:

Employee | Positions 
====================
Mike  | Janitor, Dustman 
Diana | Dishwasher 
Emily | NULL

Thank you in advance!

@gretal, your link points to a MS SQL Server question; this is **Oracle**. — Littlefoot, Dec 07 '21 at 08:13
oh i have not noticed that .this is for oracle:https://stackoverflow.com/questions/45278653/oracle-query-join-with-comma-separated-data — gretal, Dec 07 '21 at 08:13

score 1 · Answer 1 · answered Dec 07 '21 at 08:11

Outer join with aggregation. Sample data in lines #1 - 11; query begins at line #12:

SQL> with
  2  employee (id, name) as
  3    (select 1, 'Mike'  from dual union all
  4     select 2, 'Diana' from dual union all
  5     select 3, 'Emily' from dual
  6    ),
  7  positions (id, position, employeeid) as
  8    (select 1, 'Janitor'   , 1 from dual union all
  9     select 2, 'Dustman'   , 1 from dual union all
 10     select 3, 'Dishwasher', 2 from dual
 11    )
 12  select e.name,
 13         listagg(p.position, ', ') within group (order by p.position) positions
 14  from employee e left join positions p on p.employeeid = e.id
 15  group by e.name
 16  order by e.name;

NAME  POSITIONS
----- ------------------------------
Diana Dishwasher
Emily
Mike  Dustman, Janitor

SQL>

How do I join table data into one comma separated column

1 Answers1