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 |