CODEWITHSUNDEEP
sqloracle-databasegroup-by
RJGordon
RJGordon

Reputation: 117

Concatenate Certain Rows in Group?

Suppose I have the following tables:

vs_tblMovies

MOVIEID   MOVIENAME  
11        Star Wars

vs_tblGenreBridge

MOVIEID   GENREID
11        878
11        28
11        12

vs_tblGenres

GENREID   GENRETITLE
28        Action    
12        Adventure
878       Science Fiction

vs_tblActors

ACTORID   STAGELNAME    STAGEFNAME
1         Lucas         George

vs_tblCastMembers

CASTMEMBERROLEID   MOVIEID  ACTORID
351                11       1
352                11       1
353                11       1

vs_tblCastMemberRoles

CASTMEMBERROLEID   CASTMEMBERROLETITLE   CASTMEMBERROLEDESC
351                Directing             Director
352                Production            Executive Producer
353                Writing               Writer

I want display all of the roles a given actor has had, with the result set being in the following format:

GENRETITLE  MOVIENAME   ACTORID STAGELNAME  STAGEFNAME  CASTMEMBERROLEID    CASTMEMBERROLEDESC

To do this, I wrote the following query (getting the roles for ActorID = 1, which is George Lucas):

SELECT vs_tblGenres.GenreTitle,
       vs_tblMovies.MovieName,
       vs_tblActors.ActorID, 
       vs_tblActors.StageLName, 
       vs_tblActors.StageFName,
       vs_tblCastMembers.CastMemberRoleID,
       vs_tblCastMemberRoles.CastMemberRoleDesc

FROM vs_tblCastMembers

  INNER JOIN vs_tblActors ON vs_tblCastMembers.ActorID = vs_tblActors.ActorID
  INNER JOIN vs_tblMovies ON vs_tblCastMembers.MovieID = vs_tblMovies.MovieID
  INNER JOIN vs_tblGenreBridge ON vs_tblMovies.MovieID = vs_tblGenreBridge.MovieID
  INNER JOIN vs_tblGenres ON vs_tblGenreBridge.GenreID = vs_tblGenres.GenreID
  INNER JOIN vs_tblCastMemberRoles ON vs_tblCastMembers.CastMemberRoleID = vs_tblCastMemberRoles.CastMemberRoleID

WHERE vs_tblActors.ActorID = 1

GROUP BY vs_tblGenres.GenreTitle, 
         vs_tblMovies.MovieName, 
         vs_tblActors.ActorID, 
         vs_tblActors.StageLName, 
         vs_tblActors.StageFName, 
         vs_tblCastMembers.CastMemberRoleID,
         vs_tblCastMemberRoles.CastMemberRole Desc

Which Outputs:

| GENRETITLE      | MOVIENAME | ACTORID | STAGELNAME | STAGEFNAME | CASTMEMBERROLEID | CASTMEMBERROLEDESC |
|-----------------|-----------|---------|------------|------------|------------------|--------------------|
| Science Fiction | Star Wars | 1       | Lucas      | George     | 352              | Executive Producer |
| Adventure       | Star Wars | 1       | Lucas      | George     | 352              | Executive Producer |
| Action          | Star Wars | 1       | Lucas      | George     | 351              | Director           |
| Adventure       | Star Wars | 1       | Lucas      | George     | 351              | Director           |
| Science Fiction | Star Wars | 1       | Lucas      | George     | 353              | Writer             |
| Science Fiction | Star Wars | 1       | Lucas      | George     | 351              | Director           |
| Action          | Star Wars | 1       | Lucas      | George     | 353              | Writer             |
| Adventure       | Star Wars | 1       | Lucas      | George     | 353              | Writer             |
| Action          | Star Wars | 1       | Lucas      | George     | 352              | Executive Producer |

What I want to do is to merge the cases where only the GenreTitle is different, so it doesn't list the role multiple times for each genre. The ideal ouput would be something like this:

| GENRETITLE                         | MOVIENAME | ACTORID | STAGELNAME | STAGEFNAME | CASTMEMBERROLEID | CASTMEMBERROLEDESC |
|------------------------------------|-----------|---------|------------|------------|------------------|--------------------|
| Action, Adventure, Science Fiction | Star Wars | 1       | Lucas      | George     | 352              | Executive Producer |
| Action, Adventure, Science Fiction | Star Wars | 1       | Lucas      | George     | 351              | Director           |
| Action, Adventure, Science Fiction | Star Wars | 1       | Lucas      | George     | 353              | Writer             |

What is the simplest way to do this in Oracle 12c?

Upvotes: 2

Views: 35

Answers (1)

GMB
GMB

Reputation: 222432

You are almost there. All you have to do is pull out Oracle LISTAGG aggregate function to agglutinate all GenreTitles together, with respect to other GROUP BY fields. Accordingly, you need to remove GenreTitle from the GROUP BY clause.

From the docs :

For a specified measure, LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates the values of the measure column.

Updated query : 

SELECT LISTAGG(vs_tblGenres.GenreTitle, ', ') WITHIN GROUP (ORDER BY vs_tblGenres.GenreTitle) AS GenreTitle,
       vs_tblMovies.MovieName,
       vs_tblActors.ActorID, 
       vs_tblActors.StageLName, 
       vs_tblActors.StageFName,
       vs_tblCastMembers.CastMemberRoleID,
       vs_tblCastMemberRoles.CastMemberRoleDesc

FROM vs_tblCastMembers

  INNER JOIN vs_tblActors ON vs_tblCastMembers.ActorID = vs_tblActors.ActorID
  INNER JOIN vs_tblMovies ON vs_tblCastMembers.MovieID = vs_tblMovies.MovieID
  INNER JOIN vs_tblGenreBridge ON vs_tblMovies.MovieID = vs_tblGenreBridge.MovieID
  INNER JOIN vs_tblGenres ON vs_tblGenreBridge.GenreID = vs_tblGenres.GenreID
  INNER JOIN vs_tblCastMemberRoles ON vs_tblCastMembers.CastMemberRoleID = vs_tblCastMemberRoles.CastMemberRoleID

WHERE vs_tblActors.ActorID = 1

GROUP BY vs_tblMovies.MovieName, 
         vs_tblActors.ActorID, 
         vs_tblActors.StageLName,
         vs_tblActors.StageFName, 
         vs_tblCastMembers.CastMemberRoleID,
         vs_tblCastMemberRoles.CastMemberRole Desc

Upvotes: 1

Related Questions