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

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

http://sqlfiddle.com/#!9/25d80c/1

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

mysqlgroup-byleft-joingroup-concat


Solution 1:[1]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Stu

Related Questions