oracle - SQL show multiple values from same colums -

hello first post here :) sorry english isn't first language.

there 2 tables , must inner join.

first table called people

name, surname, id, , value   

second table called work

id (external key of fist table), category, state, roles, date.  

column "roles" can have multiple values (employee director workers etc).

i must show inner join history of roles each people in 1 row ( name surname roles1, roles 2 roles3)

example jack brown employee director workers

how can show in 1 row multiple values contained in 1 columns ?

if need see roles don't require them in separate columns can use listagg()

select p.id,         p.name,         p.surname,         listagg(w.roles, ',') within group (order start_date) all_rows people p   join work w on p.id = w.id group p.id, p.name, p.surname 

this output this:

id | name   | surname | all_roles                 ---+--------+---------+-------------------------  1 | jack   | brown   | employee,worker,director 

---

you can't have each role in separate column, because in sql number of columns in result fixed. can't have result has 3 columns roles of "jack brown" , 2 column roles of "arthur dent".