SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE [T1](
	[emp_id] [int] NULL,
	[name] [nvarchar](50) NULL
)

insert into t1
values (1,'George'),(2,'Josef'),(3,'Bill'),(4,'Jack'),(5,'Toti')



CREATE TABLE T2 
(
TICKET NVARCHAR(5) NULL,
EMP_ID INT NULL,
RANK_OF_TICKET INT
)

INSERT INTO T2 
VALUES ('001',1,1),('002',1,2),('003',1,3),('004',2,1),('005',3,1),('006',3,2),('007',3,3),('008',3,4)

13 rows affected

select * from t1
select * from t2


emp_id
name


1
George

2
Josef

3
Bill

4
Jack

5
Toti




TICKET
EMP_ID
RANK_OF_TICKET


001
1
1

002
1
2

003
1
3

004
2
1

005
3
1

006
3
2

007
3
3

008
3
4


select t1.* , t2_1.ticket 'first_ticket', t2_2.ticket '2nd_ticket'
,t2_3.ticket '3rd_ticket', t2_4.ticket '4th_ticket', t2_5.ticket '5th_ticket'
from t1 
left join t2 AS t2_1 on t1.emp_id = t2_1.emp_id AND t2_1.RANK_OF_TICKET = 1
left join t2 AS t2_2 on t1.emp_id = t2_2.emp_id AND t2_2.RANK_OF_TICKET = 2
left join t2 AS t2_3 on t1.emp_id = t2_3.emp_id AND t2_3.RANK_OF_TICKET = 3
left join t2 AS t2_4 on t1.emp_id = t2_4.emp_id AND t2_4.RANK_OF_TICKET = 4
left join t2 AS t2_5 on t1.emp_id = t2_5.emp_id AND t2_5.RANK_OF_TICKET = 5






emp_id
name
first_ticket
2nd_ticket
3rd_ticket
4th_ticket
5th_ticket


1
George
001
002
003
null
null

2
Josef
004
null
null
null
null

3
Bill
005
006
007
008
null

4
Jack
null
null
null
null
null

5
Toti
null
null
null
null
null

emp_id	name	first_ticket	2nd_ticket	3rd_ticket	4th_ticket	5th_ticket
1	George	001	002	003	null	null
2	Josef	004	null	null	null	null
3	Bill	005	006	007	008	null
4	Jack	null	null	null	null	null
5	Toti	null	null	null	null	null