How to output every combination of values in 2 columns, in groups?

I have a table where I want every parent to be mapped to every child within each group.

Input:

group_id     parent     child
1            A          E
1            B    
2            C          F
2            D
2            E          G      
3            X
3            Y

Output:

group_id     parent     child
1            A          E
1            B          E
2            C          F
2            D          F
2            E          F      
2            C          G
2            D          G
2            E          G

So within each group, I want every value in parent to map to every value in child. If there are no values in child for a group, I want that group completely omitted (as shown where group_id = 3)

I was originally thinking of using GROUP BY 1, 2 and aggregating by something like MAX(child), but then I came across edge cases in my data where there may be >1 child. I also tried using CROSS JOIN but I'm struggling with getting my desired output. Thanks in advance.

CodePudding user response：

Disclaimer, I don't use Redshift so there may be better options. However, a CROSS JOIN should work. Just grab the DISTINCT parent values for all groups. Then do the same for the child values, and JOIN the two results together

SELECT  p.group_id, p.parent, c.child
FROM   ( 
           SELECT group_id, parent
           FROM   YourTable
           GROUP BY group_id, parent
       ) 
       p CROSS JOIN 
       (
           SELECT group_id, child
           FROM   YourTable
           WHERE  child IS NOT NULL
           GROUP BY group_id, child       
       )
       c 
WHERE  p.group_id = c.group_id
ORDER BY p.group_id, c.child, p.parent

Results:

group_id	parent	child
1	A	E
1	B	E
2	C	F
2	D	F
2	E	F
2	C	G
2	D	G
2	E	G

db<>fiddle here

CodePudding user response：

You can do it exploiting a variable. The variable will be set to the value of child if its value is not null, otherwise it will copy over the value contained in the variable

SET @child := "";
SELECT 
    group_id,
    parent,
    IF(child IS NULL, @child, @child := child) AS child
FROM 
    tab

Here's a fiddle: https://www.db-fiddle.com/f/5KnoFogV19jc4nWa8rrzVx/0.

Does it work for you?