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');

SELECT * FROM #Players

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