clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36250 in the last week).

CREATE TABLE #Players( [Name] [nvarchar](50) NOT NULL, [Surname] [nvarchar](50) NOT NULL, [Team] [nvarchar](50) NULL, ) ON [PRIMARY];
 hidden batch(es)

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
 hidden batch(es)

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
 hidden batch(es)

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
 hidden batch(es)