Is there a way to split a string and regroup the values like below in Postgresql.
input_column
abc.1, xyz.1, abc.2, abc.3 xyz.3
result_column
abc{1,2,3}, xyz{1,3}
1 Answer
You can first split the items into rows and the aggregate back:
select code, array_agg(value) as values
from (
select split_part(item, '.', 1) as code,
split_part(item, '.', 2) as value
from the_table t
cross join regexp_split_to_table(t.input_column, '\s*,\s*') as x(item)
) t
group by code
order by code
I used regexp_split_to_table() to generate the rows rather than string_to_array() and unnest() so deal with the whitespace after the comma. With a combination of unnest() and string_to_array() this would require an additional trim() that makes the inner query a bit harder to read.
abc.3 xyz.3beabc.3, xyz.3(comma vs. space)?