MySQL: Group and flatten elements on a column

group-by, group-concat, mysql, select, sql

Solution

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

Problem

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`)

Original source

Related problems