How to retrieve all grand parents at once

Question

I have two simple tables in SQLITE3 (family bonds):

"persons" (id, fname, lname)
"relationships" (parent/child)

I would like to get each grand children along with all their grand parents (from 1 to 4 of them depending on the grand child) so that 1 row result =

Distinct Grand child, Grand parent 1, Grand parent 2, Grand parent 3, Grand parent 4

Thanks to Caius Jard, I've been able to get each child and its grand parents in another star overflow question. However, so far I have:
1 line = Grand child, 1 grand parent (so it needs up to 4 lines to get all grand parents of a child).

SELECT c.fname, c.lname, gp.fname, gp.lname  
FROM relations p_c
INNER JOIN relationships gp_p ON gp_p.child = p_c.parent 
INNER JOIN persons gp ON gp.id = gp_p.parent 
INNER JOIN persons c ON c.id = p_c.child
ORDER BY c.id;

How could I edit this so that I get each grand child along with all the grand parents in one single row result?

If possible only using SELECT (+ count/distinct), WHERE (+in/exists), INNER/LEFT JOIN, GROUP BY (+having), ORDER BY.

Answer 1

Assuming there are four grandparents, you can enumerate them and aggregate:

SELECT fname, lname,
       MAX(CASE WHEN seqnum = 1 THEN grandparent_name END) as grandparent_1,
       MAX(CASE WHEN seqnum = 2 THEN grandparent_name END) as grandparent_2,
       MAX(CASE WHEN seqnum = 3 THEN grandparent_name END) as grandparent_3,
       MAX(CASE WHEN seqnum = 4 THEN grandparent_name END) as grandparent_4
FROM (SELECT c.id, c.fname, c.lname,
             gp.fname || ' ' || gp.lname as grandparent_name,
             ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY gp.fname, gp.lanme) as seqnum 
      FROM relations p_c JOIN
           relationships gp_p
           ON gp_p.child = p_c.parent JOIN
           persons gp
           ON gp.id = gp_p.parent 
           persons c
           ON c.id = p_c.child
     ) cgp
GROUP BY fname, lname, id;