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 |