MySQL: Group and flatten elements on a column

Question

If I have a view:

Movie             Genre    Actor
-------------------------------------------
Ocean's Twelve    Crime    George Clooney
Ocean's Twelve    Crime    Julia Roberts
Ocean's Twelve    Crime    Brad Pitt
Forrest Gump      Drama    Tom Hanks

How would I group by the movie title, but flatten the other columns like so:

Movie             Genre    Actor
-------------------------------------------
Ocean's Twelve    Crime    George Clooney, Julia Roberts, Brad Pitt
Forrest Gump      Drama    Tom Hanks

Note that if an element is equivalent, it is not repeated (e.g. Crime)

Saharsh Shah · Accepted Answer · 2014-01-28T10:40:06.090

4

MySQL

Use GROUP_CONCAT() function:

SELECT movie, Genre, GROUP_CONCAT(Actor) AS Actor
FROM tableA
GROUP BY movie, Genre

SQL SERVER

SELECT A.movie, A.Genre, MAX(STUFF(B.ActorNames, 1, 1, '')) AS Actor
FROM tableA A 
CROSS APPLY(SELECT ' ' + Actor + ',' FROM tableA B 
            WHERE A.movie = B.movie AND A.Genre = B.Genre 
            FOR XML PATH('')
          ) AS B (ActorNames)
GROUP BY A.movie, A.Genre

edited Jan 28 '14 at 10:40

answered Jan 28 '14 at 10:22

Saharsh Shah

28,687
8
48
83

score 2 · Answer 2 · answered Jan 28 '14 at 10:22

2

You are looking for GROUP_CONCAT

select Movie, Genre, group_concat(Actor separator ', ') as `Actors`
from
movies
group by Movie, Genre;

answered Jan 28 '14 at 10:22

StuartLC

104,537
17
209
285

how to do this in sql server ? – Mudassir Hasan Jan 28 '14 at 10:27
@mhasan Use one of these workarounds [here](http://stackoverflow.com/questions/8868604/sql-group-concat-function-in-sql-server) and [here](http://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server). I usually use the `XML PATH` hack. – StuartLC Jan 28 '14 at 10:32

score 1 · Answer 3 · answered Jan 28 '14 at 10:21

1

You need group_concat and group by http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat Try this

select Movie, Genre, group_concat(Actor) from view_name group by Movie

answered Jan 28 '14 at 10:21

naveen goyal

4,571
2
16
26

MySQL: Group and flatten elements on a column

3 Answers3