Question 114340 in PostgreSQL Question posted in
The official documentation can be found here.

How to inverse a key to array in PostgreSQL?

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      |
Tags:

2

Answers


  1. 0 
    select   regexp_split_to_table(pets, 'Ws') as pet
        ,string_agg(name, ', ')              as names
from     t
group by regexp_split_to_table(pets, 'Ws')
    pet names
    cat bob, steve
    dog bob, dave
    parrot steve

    Fiddle

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search