An example of using the array_agg() PostgreSQL function.

array_agg_example.sql

select
  pc_games.title,
  to_char(pc_games.release_date, 'YYYY-MM-DD') as release_date
  array_to_string(array_agg(avid_fans.firstname || ' ' || avid_fans.surname), ', ', null) as fans
from
  schema_A.pc_games as pc_games
  inner join schema_A.avid_fans as avid_fans on (pc_games.id = avid_fans.fk_pc_game_id)
where
  pc_games.release_date > '2004-12-31'
group by
  pc_games.title,
  pc_games.release_date,
  avid_fans.firstname,
  avid_fans.surname
 
-- Here we are selecting the particulars of all PC games released since 2016 along with all their fans.
-- Because each PC game likely has more than one fan, we are going to see a lot of repeat data.
-- Each record will contain the details of the PC game along with the name and surname of one fan.
-- If a game has ten fans we are going to see the same game's details ten times folowed by each fan's name & surname.
-- array_agg will place all the fans' names and surnames in a single field separated by commas.

-- So instead of returning:
-------------------------------------------------------------------------
-- title                             | release_date | fans             --
-------------------------------------------------------------------------
-- Prince of Persia: The two thrones | 2005-06-15   | Pierre Joubert   --
-- Prince of Persia: The two thrones | 2005-06-15   | Daniel France    --
-- Prince of Persia: The two thrones | 2005-06-15   | Mariska Etsebeth --
-------------------------------------------------------------------------

-- The query will return:
----------------------------------------------------------------------------------------------------------
-- title                             | release_date | fans                                              --
----------------------------------------------------------------------------------------------------------
-- Prince of Persia: The two thrones | 2005-06-15   | Pierre Joubert, Daniel France, Mariska Etsebeth   --
----------------------------------------------------------------------------------------------------------