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


DECLARE @Names VARCHAR(MAX) 

SELECT @Names = COALESCE(@Names + ', ', '') + [Name] 
FROM #Players

SELECT @Names AS [Names]


Names


Mary, Lory, Fiona, John, Susan, Albert, 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