mysqlpostgresqlselectdenormalized

Simple SELECT query over denormalize data

The movie lens data have denormalized genres:

movie1  ACTION|ADVENTURE
movie2  ACTION|ADVENTURE|DRAMA
...

Is it possible to write a simple SQL to get the normalized movie-genres:

movie1  ACTION
movie1  ADVENTURE
movie2  ACTION
...

Suppose I'm doing this in MySQL or PostgreSQL.

Solution

  • For PostgreSQL, you can use unnest with string_to_array:

    select name, unnest(string_to_array(genres, '|'))
from movies;