PostgreSQL how to generate each Parent Product so as to have all the Child product (and Child of chi-CodePudding

This might look a little trivial to most PostgreSQL(or SQL) experts, but since I am fairly new to this, I'm having a little hard time coming up with a sound logical rule to solve this problem.

The following is a Bill of Materials for Product A stored in a table (the two fields – ChildProduct and ParentProduct) in a SQL Database:

CREATE TABLE Table2
(ChildProduct varchar(20), ParentProduct varchar(20));

INSERT INTO Table2
(ChildProduct, ParentProduct)
Values
('Product B', 'Product A'),
('Product C', 'Product A'),
('Product D', 'Product A'),
('Product B1', 'Product B'),
('Product B2', 'Product B'),
('Product D1', 'Product D'),
('Product E1', 'Product D1'),
('Product E2', 'Product D1');

Using PostgreSQL, I would like to generate the following output from the above table:

ChildProduct    ParentProduct
Product B   Product A
Product B1  Product A
Product B2  Product A
Product C   Product A
Product D   Product A
Product D1  Product A
Product E1  Product A
Product E2  Product A
Product B1  Product B
Product B2  Product B
Product D1  Product D
Product E1  Product D
Product E2  Product D
Product E1  Product D1
Product E2  Product D1

CodePudding user response：

just columns in reverse order

with recursive pc as (
Select p.ParentProduct, p.ChildProduct 
from table2 p left outer join table2 c on p.ParentProduct=c.ChildProduct
where c.ParentProduct is null
Union
Select pc.ParentProduct, t.ChildProduct
from pc join table2 t on (pc.ChildProduct=t.ParentProduct)
)
Select * from pc
union
Select ParentProduct, ChildProduct from table2
order by 1,2;