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

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
– Gordon Linoff, Commented Sep 25, 2015 at 0:25
Yes @GordonLinoff, i'm using Oracle. Sorry, I would tag it nex time. — Gordon Shumway
– Gordon Shumway, Commented Sep 29, 2015 at 23:37

APC · Accepted Answer · 2015-09-25 06:41:11Z

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, 2015 at 6:41

APC

147k20 gold badges180 silver badges289 bronze badges

answered Sep 25, 2015 at 0:25

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gordon Shumway Over a year ago

thxs Gordon its perfect! :)

Gordon Shumway Over a year ago

I'm getting ORA-01489 error (result of string concatenation is too long) sometimes, do you know what it can be? @gordon

Gordon Linoff Over a year ago

@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: blogs.oracle.com/datawarehousing/entry/….

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related