How to query this in raw SQL?

I have a table user with 2 columns, name and role. I want to group together for one role all the names which are associated with it.

So suppose for the role “admin” is used by 3 different names.

["admin", "john"] 
["admin", "doe"]
["admin", "max"]

I want to start my query with this

from u is users, where: u.role == "admin"

Answer

listagg function should help. need to group the column containing “admin” then it will create a list of the names. basic syntax below.

select 
admin_column, 
listagg (name_column, ', ') as names
from wherever 
group by 1;