Concatenate rows

Team
TeamId TeamName
1 SAS
2 CLE
Player
PlayerId PlayerName TeamId
1 Kawhi Leonard 1
2 Manu Ginobili 1
3 Tony Parker 1
4 Lebron James 2
5 Kyrie Irving 2
6 Kevin Love 2 
	
	SELECT T1.TeamName,
		   LEFT(T1.Players, LEN(T1.Players) - 1) AS "Players"
	FROM
	(
		SELECT DISTINCT TOP 2
			   Team.TeamName,
		(
			SELECT Player.PlayerName + ', ' AS [text()]
			FROM Player
			WHERE Player.TeamId = Team.TeamId
			ORDER BY Player.PlayerName
			FOR XML PATH('')
		) Players
		FROM Team
	) T1

Result

TeamName Players
SAS Kawhi Leonard, Manu Ginobili, Tony Parker
CLE Kevin Love, Kyrie Irving, Lebron James
0
1

Related Articles