title: Oracle SQL - How to show one to many relationship in a single row?
body: <p>I have a table called MY_MOVIES with two columns: MOVIES and GENRES like this (the relationship is one movie to many genres):</p>
  
  <pre><code>GENRE         MOVIE
  ----------    --------------           
  ACTION        MOVIE1
  DRAMA         MOVIE1
  CRIME         MOVIE2
  DRAMA         MOVIE2
  CRIME         MOVIE3
  DRAMA         MOVIE3
  ACTION        MOVIE4
  ADVENTURE     MOVIE4
  FANTASY       MOVIE4
  ANIMATION     MOVIE5
  ADVENTURE     MOVIE5
  COMEDY        MOVIE5
  </code></pre>
  
  <p>This table have more than 100000 rows.</p>
  
  <p>I'm trying to get an output like this:</p>
  
  <pre><code>MOVIES        GENRES
  ----------    -------------- 
  MOVIE1        ACTION, DRAMA
  MOVIE2        CRIME, DRAMA
  MOVIE3        CRIME, DRAMA
  MOVIE4        ACTION, ADVENTURE, FANTASY
  MOVIE5        ANIMATION, ADVENTURE, COMEDY
  </code></pre>
  
  <p>I was trying with PIVOT like this (having the idea of replace 0 and 1 to genres later):</p>
  
  <pre><code>SELECT * FROM MY_MOVIES 
  PIVOT (COUNT(MOVIE) FOR GENRE in (SELECT DISTINCT(GENRE) FROM MY_MOVIES) as MOVIE_GENRES
  </code></pre>
  
  <p>I have been thinking I'm doing it the wrong way.</p>