Simple SELECT query over denormalize data

Question

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.

Answer 1

For PostgreSQL, you can use unnest with string_to_array:

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

• SQL Fiddle Demo