Not sure if I have got the wording correct here, but I have this table:

| name  | pets          |
|-------|---------------|
| bob   | cat, dog    |
| steve | cat, parrot |
| dave  | dog         |

and I want it to become this:

| pet    | names        |
|--------|--------------|
| dog    | bob, dave  |
| cat    | bob, steve |
| parrot | steve      |

CodePudding user response：

select   regexp_split_to_table(pets, '\W\s') as pet
        ,string_agg(name, ', ')              as names
from     t
group by regexp_split_to_table(pets, '\W\s')

Fiddle

CodePudding user response：

You can unnest the array and use a cross join to regroup:

select v, array_agg(t.name) from tbl t cross join unnest(t.pets) v group by v

See fiddle.