MySQL 8.0

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

select version();


version()


8.0.23


DROP TABLE IF EXISTS testPivot ;
CREATE TABLE testPivot(i int, name varchar(20), k int);



INSERT INTO testPivot VALUES
   (1,'1Player1',1),
   (1,'1Player2',2),
   (1,'1Player3',2),
   (1,'1Player4',2),
   (1,'1Player5',3),
   (1,'1Player6',4),
   (1,'1Player7',5),
   (2,'2Player1',1),
   (2,'2Player2',2),
   (2,'2Player3',2),
   (2,'2Player4',2);
   
   
SELECT
  i as team,
  max(case when right(name,1)='1' then name else '' end) as '1',
  max(case when right(name,1)='2' then name else '' end) as '2',
  max(case when right(name,1)='3' then name else '' end) as '3',
  max(case when right(name,1)='4' then name else '' end) as '4',
  max(case when right(name,1)='5' then name else '' end) as '5',
  max(case when right(name,1)='6' then name else '' end) as '6',
  max(case when right(name,1)='7' then name else '' end) as '7'
FROM testPivot
GROUP BY i;


team
1
2
3
4
5
6
7


1
1Player1
1Player2
1Player3
1Player4
1Player5
1Player6
1Player7

2
2Player1
2Player2
2Player3
2Player4

team	1	2	3	4	5	6	7
1	1Player1	1Player2	1Player3	1Player4	1Player5	1Player6	1Player7
2	2Player1	2Player2	2Player3	2Player4