CREATE TABLE #team_members
	(
	id numeric(10) identity,
	Name varchar(100),
	Team varchar(100)
	)

INSERT INTO #team_members SELECT 'Vasili', 'R&D'
INSERT INTO #team_members SELECT 'Peter1', 'HR'
INSERT INTO #team_members SELECT 'Peter2', 'HR'
INSERT INTO #team_members SELECT 'Maxim',  'R&D'
INSERT INTO #team_members SELECT 'Peter3', 'HR'
INSERT INTO #team_members SELECT 'David',  'Security'





The table #team_members will look like this:

id	Name	Team
1	Vasili	R&D
2	Peter1	HR
3	Peter2	HR
4	Maxim	R&D
5	Peter3	HR
6	David	Security




Then we create #t table, with a blank field of 1000 chars named MembersList, 
and fill it with ordered by our grouping field list of values from #team_members:

SELECT Name, Team, replicate(' ',1000) as MembersList
INTO #t
FROM #team_members
ORDER BY Team


DECLARE @List varchar(1000)
SET @List=''

DECLARE @PrevTeam varchar(100)
SET @PrevTeam=''

UPDATE #t SET
	@List = MembersList = (CASE WHEN Team=@PrevTeam then (@List + ',' + Name) ELSE Name END),
	@PrevTeam = Team

SELECT DISTINCT 
	Team, 
	(
	SELECT TOP 1 q.MembersList 
	FROM #t as q 
	WHERE q.Team=#t.Team 
	ORDER BY LEN(q.MembersList) DESC
	) AS Members 
FROM #t


In the end we get:

#t:

Name	Team	MembersList
Peter1	HR	Peter1
Peter2	HR	Peter1,Peter2
Peter3	HR	Peter1,Peter2,Peter3
Maxim	R&D	Maxim
Vasili	R&D	Maxim,Vasili
David	Security	David

And the last select:
Team	Members
HR	Peter1,Peter2,Peter3
R&D	Maxim,Vasili
Security	David