Join two tables and add the result of join to dynamic columns

Dear,I have the following Tables:

I joined the tables together using emp_id

Here tables and my solution. i think it is not the ideal solution any ideas please.

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=cdfa3fd85b83ef78812dbfec7ca9d589

Answer

Using 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

check https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=02cd5aeb07976c5dcda88124d69291c4