sql - How to extract repeated values from a database table

Question

Is it possible extract the values of 'score' as shown below when multiple values of passageId are identical for each value of userId.

userId passageId score
1      1         2
1      2         3
1      1         4
1      1         5
2      1         3
2      3         3
2      3         4

Result:

userId passageId scores
1      1         2, 4, 5
1      2         3
2      1         3
2      3         3, 4

I was advised to use the following code, but I need more than two values to be extracted:

SELECT 
   userId,
   passageId,
   min(score) as score_1,
   max(score) as score_2
FROM mytable
GROUP BY    
   userId,
   passageId
HAVING COUNT(*)>=2;

I was also advised to use string_agg but could not make it work in pgAdmin.

question from:https://stackoverflow.com/questions/65908941/how-to-extract-repeated-values-from-a-database-table

Answer 1

pgadmin suggests that you are using Postgres. That in turn suggests that you should use string_agg() or array_agg():

SELECT userId, passageId, ARRAY_AGG(score)
FROM mytable
GROUP BY userId, passageId
HAVING COUNT(*) >= 2;