How to concatenate group members into a resulting column of an SQL query

Question

I have 2 related tables as given below where I need to concatenate the names of group members into a query result as given below:

I am not sure whether this is possible with SQL. I tried to use group by, but I do not know a useful expression to search for concatenation in the context of group by. Any hints where and with what key words to search for will be gratefully appreciated.

It looks like you're really asking how to do something similar to MySQL's `group_concat`. Have you tried searching for that? — ProgrammingLlama, Mar 13 '22 at 14:57
No, I did not know that there is a feature such as group_concat. I will look into it — Manu, Mar 13 '22 at 14:59
Be aware that `group_concat` is a MySQL feature, so you would have to look for the equivalent in Microsoft SQL Server's syntax. — ProgrammingLlama, Mar 13 '22 at 15:10

score 2 · Accepted Answer · answered Mar 13 '22 at 15:01

use String_agg as follows

SELECT groupid,
       groupname,
       String_agg(groupmembername, ',') AS GroupMemberName
FROM   lefttable1 t1
       JOIN righttable2 t2
         ON t1.groupid = t2.groupid
GROUP  BY groupid,
          groupname

or as follows

SELECT groupid,
       groupname,
       String_agg(groupmembername, ',') AS GroupMemberName
FROM   (SELECT groupid,
               groupname,
               groupmembername
        FROM   lefttable1 t1
               JOIN righttable2 t2
                 ON t1.groupid = t2.groupid) T3

How to concatenate group members into a resulting column of an SQL query

1 Answers1