-3

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:

enter image description here

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.

Manu
  • 810
  • 4
  • 12
  • 28
  • 1
    It looks like you're really asking how to do something similar to MySQL's `group_concat`. Have you tried searching for that? – DiplomacyNotWar 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
  • 1
    Be aware that `group_concat` is a MySQL feature, so you would have to look for the equivalent in Microsoft SQL Server's syntax. – DiplomacyNotWar Mar 13 '22 at 15:10

1 Answers1

2

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
RF1991
  • 1,408
  • 1
  • 6
  • 12