How can I create a Group_Concat field per table using values from other records-CodePudding

Suppose a table of First and Last Names and for each record I want to do comma-delimited list of relatives.

1ST | LAST | RELATIVES
Bob | Smith | Alice,Andrew
Alice | Smith | Bob,Andrew
Andrew |Smith | Bob,Alice
Alex | Jones | Anny, Ricky
Anny | Jones | Alex, Ricky
Ricky | Jones | Alex, Anny

As per this sqlFiddle

I know how to group_contact manually for any last name but am unclear how for each record I could have it go find the records with matching last name and run the same group_concat

CodePudding user response：

You can do it with a self LEFT join and aggregation:

SELECT s1.First, s1.Last,
       GROUP_CONCAT(s2.First) Relatives
FROM Surnames s1 LEFT JOIN Surnames s2
ON s2.Last = s1.Last AND s2.First <> s1.First
GROUP BY s1.First, s1.Last;

See the demo.

CodePudding user response：

You can put the aggregation in a lateral join, like so:

select s.First, s.Last, r.Relatives
from Surnames s,
lateral (
  select group_concat(First) Relatives
  from Surnames r
  where s.Last = r.Last AND s.First != r.First
)r

DB<>Fiddle