Oracle SQL - How to show one to many relationship in a single row?

Question

I have a table called MY_MOVIES with two columns: MOVIES and GENRES like this (the relationship is one movie to many genres):

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

This table have more than 100000 rows.

I'm trying to get an output like this:

MOVIES        GENRES
----------    -------------- 
MOVIE1        ACTION, DRAMA
MOVIE2        CRIME, DRAMA
MOVIE3        CRIME, DRAMA
MOVIE4        ACTION, ADVENTURE, FANTASY
MOVIE5        ANIMATION, ADVENTURE, COMEDY

I was trying with PIVOT like this (having the idea of replace 0 and 1 to genres later):

SELECT * FROM MY_MOVIES 
PIVOT (COUNT(MOVIE) FOR GENRE in (SELECT DISTINCT(GENRE) FROM MY_MOVIES) as MOVIE_GENRES

I have been thinking I'm doing it the wrong way.

You should tag the question with the database you are using (presumably Oracle, given the PL/SQL tag). — Gordon Linoff, Sep 25 '15 at 00:25
Yes @GordonLinoff, i'm using Oracle. Sorry, I would tag it nex time. — Gordon Shumway, Sep 29 '15 at 23:37

score 9 · Accepted Answer · edited Sep 25 '15 at 06:41

9

Assuming that you are using Oracle (version 11gR2 or later):

select movies,
       listagg(genre, ', ') within group (order by genre) as genres
from my_movies
group by movies;

edited Sep 25 '15 at 06:41

APC

144,005
19
170
281

answered Sep 25 '15 at 00:25

Gordon Linoff

1,242,037
58
646
786

thxs Gordon its perfect! :) – Gordon Shumway Sep 29 '15 at 23:38
I'm getting ORA-01489 error (result of string concatenation is too long) sometimes, do you know what it can be? @gordon – Gordon Shumway Oct 02 '15 at 23:20
@GordonShumway . . . There is a hard limit of 4,000 for `listagg()`. Here is a blog that finds at least one way to address the issue: https://blogs.oracle.com/datawarehousing/entry/managing_overflows_in_listagg. – Gordon Linoff Oct 03 '15 at 02:23

Oracle SQL - How to show one to many relationship in a single row?

1 Answers1