SQL Server Concatenating Values From Several Rows

Question

So I have a database I'm working with which contains two tables:

Player
Times

On the front end, the data from these tables is combined into a table like so:

playerName | Times
Player1    | 1.0 2.0 4.5 7.5
Player2    | 1.5 4.3 7.8 2.0 4.5

So as you can, a player can have many times in that column. My issue is getting those times and concatenating them into one field.

The two tables in the db are related via an ID field. The Times table contains a composite key consisting of that ID field as well as an ID for the time. I.e. each individual time is its own row.

How can I grab each of these times and concatenate them into one field for each player? I cannot have multiple rows returned by the query for each player as there are other fields from the players table being used elsewhere. That would cause the front end to show many rows as well, which is not an option.

Answer 1

Something like this:

SELECT P.PlayerName,
    AT.Times
FROM Player P
CROSS APPLY
(
  SELECT CAST(Time as varchar) + ' ' as [text()]
  FROM Times T
  WHERE P.Id = T.PlayerId
  FOR XML PATH('')
)AT(Times)

SQL FIDDLE DEMO