You can get results like that with or without joining the 2 tables.
All the information seems to be in the per_assignments_x table, so you can do this:
, LISTAGG (job_id, ', ') WITHIN GROUP (ORDER BY job_id) AS job_id_list
GROUP BY person_id
but if you need the other table for some reason, then you can join it.
I hope this answers your question.
If not, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
Explain, using specific examples, how you get those results from that data. Be precise; from your first message, I can't even tell how many columns you want in the result set.
Always say which version of Oracle you're using (e.g., 220.127.116.11.0).
See the forum FAQ: Re: 2. How do I ask a question on the forums?
I mean how to write the query based on my initial request. I was trying to do that using PIVOT but not successful.
If you want each job_id in a separate column, then you can use PIVOT, but unless you're getting XML output, you'll have to hard-code the number of columns. That is, you can say that there will be up to 10 jobs per person. If there happen to be few for any person (or all persons) then some of the columns will be NULL. If there happen to be more than 10 for a given person, only 10 can be displayed, and the others will be ignored. If that's what you want, post the information I mentioned in reply #1. Post your best attempt using PIVOT, also. You might have the solution already, except for some small, easily-fixed misunderstanding.