create a postgresql view from a unique list of exploded data

You can split the string to rows with regexp_split_to_table() in a lateral join, then aggregate:

select x.val, count(*) cnt
from mytable t
cross join lateral regexp_split_to_table(t.mycol, '\s/\s') as x(val)
group by x.val

Demo on DB Fiddle:

val   | cnt
:---- | --:
bar   |   2
foo   |   3
hello |   2

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top