SQL Concatenate based on string inclusion

user_11767482 0
Viewed 51

I have the following SQL table:

COL_A      || COL_B    ||
=========================
aa         ||    1     ||
aa         ||    2     ||
aa.bb      ||    3     ||  
aa.bb.cc   ||    4     ||  
aa.bb.cc   ||    5     ||
dd         ||    6     ||
dd.ee      ||    7     ||

As part of a SELECT query, I'd like to group by the values of Col_A and concatenate the values in Col_B based on the values in Col_A being a subset of one another. Meaning, if a value of Col_A is contained by/is equal to another value of Col_A, the corresponding Col_B of the superset/same Col_A value should be concatenated together.

Desired result:

COL_A      || COL_B                 ||
======================================
aa         ||   [1, 2, 3, 4, 5]     ||
aa.bb      ||   [3, 4, 5]           ||  
aa.bb.cc   ||   [4, 5]              ||  
dd         ||   [6, 7]              ||
dd.ee      ||   [7]                 ||
postgresql sql
2 Answers

You can use a self join with array_agg:

select t1.col_a, array_agg(distinct t2.col_b) 
from vals t1 join vals t2 on t2.col_a ~ t1.col_a 
group by t1.col_a order by t1.col_a
user_73267380

You can do this using a lateral join

select t.cola, Concat('[',x.colB,']') ColB
from t
left join lateral (
    select string_agg(colb::character,',') colB
    from t t2
    where t2.cola ~ t.cola
)x on true
group by t.cola, x.colb;

Working fiddle

user_153326500
Related