Rejoignez deux tables et ajoutez le résultat de la jointure à des colonnes dynamiques

Voici comment vous y arriveriez à l'aide de pivot xxx

Ceci peut être fait avec une agrégation conditionnelle.

select 
  x.emp_id
, x.name
, max(first_ticket) as [first_ticket]
, max(second_ticket) as [second_ticket]
, max(third_ticket) as [third_ticket] 
, max(fourth_ticket) as [fourth_ticket] 
, max(fifth_ticket) as [fifth_ticket] 
from (
select 
  t1.*
, case when t2.RANK_OF_TICKET = 1 then t2.TICKET else null end as [first_ticket]
, case when t2.RANK_OF_TICKET = 2 then t2.TICKET else null end as [second_ticket]
, case when t2.RANK_OF_TICKET = 3 then t2.TICKET else null end as [third_ticket]
, case when t2.RANK_OF_TICKET = 4 then t2.TICKET else null end as [fourth_ticket]
, case when t2.RANK_OF_TICKET = 5 then t2.TICKET else null end as [fifth_ticket]
from t1
left join t2 on t1.emp_id = t2.emp_id
) as x
group by 
  x.emp_id
, x.name
order by x.emp_id

Utilisation de CTE

with mytable as 
(
select t1.* , t2.TICKET,t2.RANK_OF_TICKET from t1 left join t2 on t1.emp_id = t2.emp_id
) 
select * from mytable 
pivot ( max(TICKET) for RANK_OF_TICKET in ( [1],[2],[3],[4]) ) as pvt
order by emp_id