By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table games (id int, ref1 int, ref2 int, ref3 int);
insert into games select 1 , 1 , 3 , 2
insert into games select 2 , 2 , 1 , 3
insert into games select 3 , 1 , 2 , 3
create table Officials (refid int, name varchar(10))
insert into officials select 1, 'Jaz'
insert into officials select 2, 'Rog'
insert into officials select 3, 'Dan'
6 rows affected
select GameId,
Max(case when seq=1 then name end) Ref1,
Max(case when seq=2 then name end) Ref2,
Max(case when seq=3 then name end) Ref3
from (
select g.id GameId, o.refId, o.Name, r.Seq
from games g
cross apply (values (ref1,1), (ref2,2), (ref3,3))r(refId,seq)
join officials o on o.refId=r.RefId
where g.id=1
)g
group by GameId
| GameId | Ref1 | Ref2 | Ref3 |
|---|---|---|---|
| 1 | Jaz | Dan | Rog |
Warning: Null value is eliminated by an aggregate or other SET operation.