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] ||
CodePudding user response：
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
CodePudding user response：
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;