SQL aggregation of columns with common value in another column

Question

I have an unresolved doubt about a query I'm making in PostgreSQL.

I have these 2 tables

PLAYER

playerID      title
1             Rondo
2             Allen
3             Pierce
4             Garnett
5             Perkins<

PLAYS

playerID      TeamID
   1             1
   1             2
   1             3
   2             1
   2             3
   3             1
   3             3

and that's my query

SELECT DISTINCT concat(N.playerID, ':', N.title), TID 
FROM player N
INNER JOIN (
 SELECT DISTINCT P.playerID  as PID,  teamID as TID
 FROM plays P
 ) AS derivedTable 
ON N.playerID = PID
ORDER BY concat

the result of the query is:

"1:Rondo"  |  1 
"1:Rondo"  |  2
"1:Rondo"  |  3
"2:Allen"  |  1
"2:Allen"  |  3
"3:Pierce" |  1
"3:Pierce" |  3

but I want something like that

"1:Rondo"  |  1, 2, 3
"2:Allen"  |  1, 3
"3:Pierce" |  1, 3

I could use an array_agg, but i really dunno how

Answer 1

For MySQL

Try this:

SELECT CONCAT(N.playerID, ':', N.title) playerTitle, 
       GROUP_CONCAT(P.TID SEPARATOR ', ') TID
FROM player N 
LEFT JOIN plays P ON N.playerID = PID 
GROUP BY N.playerID
ORDER BY playerTitle

Answer 2

Use string_agg()

SELECT concat(N.playerID, ':', N.title), 
       string_agg(p.TeamID::text, ',') as teamid_list
FROM player N
  JOIN plays p ON n.playerID = p.playerID
GROUP BY n.playerID, n.title
ORDER BY 1;

Your derived table is not necessary (and the distinct even more so)

Answer 3

In Postgres should be:

SELECT concat(N.playerID, ':', N.title) title, string_agg(P.TID,', ') TID
FROM player N 
LEFT JOIN plays P ON N.playerID = P.PID 
GROUP BY 1
ORDER BY 1