MariaDB 10.5

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

    CREATE TABLE TeamPlayers
    (
    ID int(11) unsigned NOT NULL AUTO_INCREMENT,
    TeamID int(11) unsigned NOT NULL,
    PlayerID int(11) unsigned,
    Name varchar(255),
    INDEX(PlayerID),
    INDEX(Name),
    PRIMARY KEY(ID)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci
INSERT IGNORE INTO TeamPlayers (ID,TeamID,PlayerID,Name) VALUES (1,15,9,'Tim Cook'), 
(2,15,NULL,'John West'), 
(3,16,NULL,'Tim Cook'), 
(4,16,5,'John West'),
(5,17,11,'John West'),
(6,18,NULL,'John West');

Records: 6  Duplicates: 0  Warnings: 0


SELECT * FROM TeamPlayers;


ID
TeamID
PlayerID
Name


1
15
9
Tim Cook

2
15
null
John West

3
16
null
Tim Cook

4
16
5
John West

5
17
11
John West

6
18
null
John West


    SELECT a.Name,MAX(a.PlayerID),MAX(b.PlayerID),GROUP_CONCAT(a.ID SEPARATOR ','), GROUP_CONCAT(b.ID SEPARATOR ',') FROM TeamPlayers a JOIN TeamPlayers b 
    ON a.TeamID=b.TeamID AND a.Name<>b.Name
    GROUP BY a.Name


Name
MAX(a.PlayerID)
MAX(b.PlayerID)
GROUP_CONCAT(a.ID SEPARATOR ',')
GROUP_CONCAT(b.ID SEPARATOR ',')


John West
5
9
2,4
1,3

Tim Cook
9
5
1,3
2,4

ID	TeamID	PlayerID	Name
1	15	9	Tim Cook
2	15	null	John West
3	16	null	Tim Cook
4	16	5	John West
5	17	11	John West
6	18	null	John West