[SQL] Concatenate fields when grouping

list-all-title-per-user-id.md

Solution 1: list all titles for each user_id

WITH users (id, title) AS (
  VALUES (1, 'Editor'),
         (1, 'Reviewer'),
         (7, 'EIC'),
         (7, 'Editor'),
         (7, 'Reviewer'),
         (7, 'Editor'),
         (19, 'EIC'),
         (19, 'Editor'),
         (19, 'Reviewer')
)
SELECT id, array_agg(title)
FROM users
GROUP BY id
ORDER BY id;

id	array_agg
1	{Editor,Reviewer}
7	{EIC,Editor,Reviewer,Editor}
19	{EIC,Editor,Reviewer}

Solution 2: list all titles for each user_id as csv

WITH users (id, title) AS (
  VALUES (1, 'Editor'),
         (1, 'Reviewer'),
         (7, 'EIC'),
         (7, 'Editor'),
         (7, 'Reviewer'),
         (7, 'Editor'),
         (19, 'EIC'),
         (19, 'Editor'),
         (19, 'Reviewer')
)
SELECT id, string_agg(title, ',')
FROM users
GROUP BY id
ORDER BY id;

id	string_agg
1	Editor,Reviewer
7	EIC,Editor,Reviewer,Editor
19	EIC,Editor,Reviewer

Solution 3: list all titles for each user_id as csv

This might be needed in case the field concatanated are not of type text

WITH users (id, title) AS (
  VALUES (1, 'Editor'),
         (1, 'Reviewer'),
         (7, 'EIC'),
         (7, 'Editor'),
         (7, 'Reviewer'),
         (7, 'Editor'),
         (19, 'EIC'),
         (19, 'Editor'),
         (19, 'Reviewer')
)
SELECT id, array_to_string(array_agg(title), ',')
FROM users
GROUP BY id
ORDER BY id;

id	array_to_string
1	Editor,Reviewer
7	EIC,Editor,Reviewer,Editor
19	EIC,Editor,Reviewer