SQL join and Bring in 1 column into 2 columns

I have two tables shown as above. I want to create a select statement that would have the following result:

Basically, I want to join on ‘Model’ and ‘Num’ columns and bring ‘Val’ vales but break it into two columns based on ‘Ver’.

SELECT 'a' as Model ,'1' as Num ,'v1' as Ver ,'9' as val INTO #RefTbl UNION ALL
SELECT 'a','2','v1','10' UNION ALL
SELECT 'a','3','v1','11' UNION ALL
SELECT 'a','1','v2','5' UNION ALL
SELECT 'a','2','v2','6' UNION ALL
SELECT 'a','3','v2','7' UNION ALL
SELECT 'b','1','v1','20' UNION ALL
SELECT 'b','1','v2','21' UNION ALL
SELECT 'b','2','v1','25' UNION ALL
SELECT 'b','2','v2','26'


SELECT '519' as ID,'a' as Model,'1' as Num INTO #OrderTbl UNION ALL
SELECT '5616','a','3' UNION ALL
SELECT '871','b','1' 


-- failed attempt
SELECT o.*, '' as v1_val, '' as v2_val FROM #OrderTbl as o left join #RefTbl as r on o.Model = r.Model and o.Num = r.Num 

Answer

You can join and do conditional aggregation:

select
    d.ID,
    d.Model,
    d.Num,
    max(case when r.Ver = 'val1' then Val end) V1_Val,
    max(case when r.Ver = 'val2' then Val end) V2_Val
from DataTbl d
inner join ReferenceTbl r 
    on  r.Model = d.Model
    and r.Num = d.Num
group by
    d.ID,
    d.Model,
    d.Num

Alternatively, you can join twice – depending on your dataset, this might (or might not) perform better:

select
    d.ID,
    d.Model,
    d.Num,
    r1.Val V1_Val,
    r2.Val V2_Val
from DataTbl d
left join ReferenceTbl r1 
    on  r1.Model = d.Model
    and r1.Num = d.Num
    and r1.Ver = 'val1'
left join ReferenceTbl r2
    on  r2.Model = d.Model
    and r2.Num = d.Num
    and r2.Ver = 'val2'