oracle - SQL select 1 to many within the same row -

i have table 1 record, ties secondary table can contain either no match, 1 match, or 2 matches.

i need fetch corresponding records , display them within same row easy using left join if had 1 or no matches tie back, however, because can 2 matches, produces 2 records.

example 1 match:

select t1.id, t1.person1, t2.owner t1 left join t2   on t1.id = t2.matchid 

output

id   person1  owner1  ---------------------- 1    john     frank 

example 2 match:

select t1.id, t1.person1, t2.owner t1 left join t2   on t1.id = t2.matchid 

output

id   person1  owner  ---------------------- 1    john     frank 1    john     peter 

is there way can formulate select output reflect following when have 2 matches:

id   person1  owner1   owner2 ------------------------------- 1    john     frank     peter 

i explored oracle pivots bit, couldn't find way make work. explored possibility of using left join on same table twice using min() , max() when fetching matches, can see myself resorting "no other option" scenario.

any suggestions?

** edit **

@ughai - using cte address issue extent, when attempting retrieve of records, details derived common table isn't showing records on left join unless specify "matchid" (case_mbr_key) value, meaning removing "where" clause, outer joins produce no records, though case_mbr_key values there in cte data.

with cte ( select temp.beas_key,         temp.case_mbr_key,        temp.fullname,        temp.birthdt,        temp.line1,        temp.line2,        temp.line3,        temp.city,        temp.state,        temp.postcd,        row_number()    over(order temp.beas_key) r   tmp_ben_assignees temp   --where temp.case_mbr_key = 4117398 ) 

the reason because row_number value, given amount of records won't 1 or 2, attempted following, getting ora-01799: column may not outer-joined subquery

--// ben assignee 1 left join cte bass1   on bass1.case_mbr_key = c.case_mbr_key    , bass1.r in (select min(r) cte a.case_mbr_key = c.case_mbr_key) --// end ba1   --// ben assignee 2 left join cte bass2   on bass2.case_mbr_key = c.case_mbr_key    , bass2.r in (select max(r) cte b b.case_mbr_key = c.case_mbr_key) --// end ba2  

** edit 2 **

fixed above issue moving row number clause "where" portion of query instead of within join clause. seems work now.

you can use cte row_number() 2 left join or pivot this.

sql fiddle

query multiple left joins

with cte  (   select matchid,owner,row_number()over(order owner) r t2 ) select t1.id, t1.person, t2.owner owner1, t3.owner owner2  t1 left join cte t2 on  t1.id = t2.matchid , t2.r = 1 left join cte t3 on t1.id = t3.matchid , t3.r = 2; 

query pivot

with cte  (   select matchid,owner,row_number()over(order owner) r t2 ) select id, person,o1,o2 t1 left join cte t2 on  t1.id = t2.matchid pivot(max(owner) r in (1 o1,2 o2)); 

output

id  person  owner1  owner2 1   john    maxwell peter