How to concatenate names based on the team name

Example 1

Assuming that we have the following table #Players which contains a list of players and thei own team.

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

We want to use the team name to generate a list of playes

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

You can find an interactive version of this example following this link .