How to concatenate values of a columns into a string

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 generate a string with all the names of the players

DECLARE @Names VARCHAR(MAX)

SELECT @Names = COALESCE(@Names + ', ', '') + [Name]
FROM #Players

SELECT @Names AS [Names]

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