DECLARE @Temp Table(
	id int,
	team int,
	company nvarchar(10),
	name nvarchar(10),
	discipline nvarchar(10)
)

INSERT INTO @Temp
	SELECT 1, 1, 'Company 1', 'User 1', 'S' UNION ALL
	SELECT 2, 1, 'Company 1', 'User 2', 'R' UNION ALL
	SELECT 3, 1, 'Company 1', 'User 3', 'C' UNION ALL
	SELECT 4, 2, 'Company 2', 'User 4', 'R' UNION ALL
	SELECT 5, 2, 'Company 2', 'User 5', 'S' UNION ALL
	SELECT 6, 2, 'Company 2', 'User 6', 'C' UNION ALL
	SELECT 7, 3, 'Company 3', 'User 7', 'C' UNION ALL
	SELECT 8, 3, 'Company 3', 'User 8', 'R' UNION ALL
	SELECT 9, 3, 'Company 3', 'User 9', 'S'

SELECT DISTINCT
	team,
	company,
	(SELECT TOP 1 [Name] FROM @Temp s WHERE s.discipline = 'S' AND s.team = t.team) As Swimmer,
	(SELECT TOP 1 [Name] FROM @Temp r WHERE r.discipline = 'R' AND r.team = t.team) As Runner,
	(SELECT TOP 1 [Name] FROM @Temp c WHERE c.discipline = 'C' AND c.team = t.team) As Cyclist

FROM
	@Temp t

GROUP BY
	team,
	company,
	Name,
	discipline