SQL Server 2019

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

CREATE TABLE #Players(
	[Name] [nvarchar](50) NOT NULL,
	[Surname] [nvarchar](50) NOT NULL,
	[Team] [nvarchar](50) NULL,
) ON [PRIMARY];
INSERT INTO #Players
  ( [Name], [Surname], [Team])
VALUES
  ('Mary', 'Green', 'cats'),
  ('Lory', 'Red', 'dogs'),
  ('Fiona', 'White', 'cats'),
  ('John', 'Yellow', 'cats'),
  ('Susan', 'De Blue', 'dogs'),
  ('Albert', 'Mac Black', 'dogs'),
  ('Marc', 'Stripes', 'referee');
7 rows affected

SELECT *
FROM #Players


Name
Surname
Team


Mary
Green
cats

Lory
Red
dogs

Fiona
White
cats

John
Yellow
cats

Susan
De Blue
dogs

Albert
Mac Black
dogs

Marc
Stripes
referee


SELECT C.Team,Left(C.Name,Len(C.Name)-1) As "Player"
FROM (
    Select distinct B.Team, 
    (
        SELECT A.Name + ',' AS [text()]
        FROM #Players A
        WHERE A.Team = B.Team
        ORDER BY A.Team
        FOR XML PATH ('')
        ) [Name]
    FROM #Players B
    ) C


Team
Player


cats
Mary,Fiona,John

dogs
Lory,Susan,Albert

referee
Marc

Name	Surname	Team
Mary	Green	cats
Lory	Red	dogs
Fiona	White	cats
John	Yellow	cats
Susan	De Blue	dogs
Albert	Mac Black	dogs
Marc	Stripes	referee