0

I am trying to get comma-separated values from the following 3 tables:

Teams: Id

Players: Id, Name

TeamsPlayers: Id, TeamId, PlayerId

What I am trying to get is a result like this:

TeamId PlayerNames
1 Ronaldo,Rivaldo,Ronaldinho,Dida
2 Riquelme,Messi,Aguero,DiMaria 
sql

SELECT STRING_AGG(Name, ',')
FROM (
    SELECT tp.TeamId, p.Name
    FROM Players p
    JOIN TeamPlayers tp ON p.Id = tp.PlayerId
    GROUP BY tp.TeamId, p.Name
    )
Sebastián Rubina
  • 89
  • 5
  • 1
    Why are you using a derived table (with an alias, which will cause an error)? YOu just need to aggregation on the name, as you're there. There's even an [example in the documentation](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver16#d-return-news-articles-with-related-tags) of exactly what you need. – Thom A Jan 24 '23 at 15:56
  • 1
    **without** an alias (nitpick police...) – Andrew Jan 24 '23 at 16:02

1 Answers1

2

Expanding on Larnu's comment.

Asssuming your initial query has the proper elements

SELECT tp.TeamId, 
      ,Names = STRING_AGG(p.Name, ',')
 FROM  Players p
 JOIN TeamPlayers tp ON p.Id = tp.PlayerId
 GROUP BY tp.TeamId
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66