'SQL table merge with special rules

I have two tables

CREATE TABLE df1 (
    src varchar(255),
    dst varchar(255)
);

insert into df1 (src, dst) 
values 
('src1', 'dst1'), 
('src2', 'dst2'),
('src3', 'dst3');

CREATE TABLE df2 (
    src varchar(255),
    dst varchar(255)
);

insert into df2 (src, dst) 
values 
('dst1', 'dstDst1'), 
('dst1', 'dstDst2'),
('dst3', 'dstDst3');

I want to merge them in this way (pseudo code):

if df1.dst = df2.src:
  df1.dst = list(df1.dst, df2.dst)
else:
  no op

using the example above, the merged table becomes

CREATE TABLE df3 (
    src varchar(255),
    dst varchar(255)
);

insert into df3 (src, dst) 
values 
('src1', 'dst1, dstDst1, dstDst2'), 
('src2', 'dst2'),
('src3', 'dst3, dstDst3');

How do I build table df3 from df1 and df2? NOTE the final table dst column can be a list or string concat, either way is fine.



Sources

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

Source: Stack Overflow

Solution Source