SQL - Join multiple rows to a table and transpose into columns

Question

So I've got two tables as below:

Opportunity:

Opp ID	Opp Name	Opp Owner
01	Opp 1	Gary
02	Opp 2	Trevor

Opportunity Split:

Opp ID	Owner	Split %
01	Gary	100%
02	Trevor	50%
02	Peter	50%

SO I then want to be able to put the split into the first table like

Opp ID	Opp Name	Opp Owner	First Owner	First Owner Split	Second Owner	Second Owner Split
01	Opp 1	Gary	Gary	100%	null	null
02	Opp 2	Trevor	Trevor	50%	Peter	50%

I'm awful with SQL so any helps appreciated.

Answer 1

Consider below approach

select * from (
  select *, 
    row_number() over(partition by Opp_ID order by Split desc) pos
  from table_1 
  left join table_2 
  using (Opp_ID)
) pivot (
  min(Owner) as Owner, min(Split) as Split for pos in (1,2,3)
)           

if applied to sample data in your question - output is