CODEWITHSUNDEEP
mysqlsql
David542
David542

Reputation: 110502

Complex concatenation on GROUP BY string

I have the following query:

SELECT
    title_imdb_id, person_imdb_id, role_type_id, role_name
FROM
    mturk_imdbcredit
WHERE
    title_imdb_id=1

Which gives me:

title_imdb_id   person_imdb_id  role_type_id    role_name
1   1588970 Actor   Herself
1   5690    Director    NULL

I would like to group by the title_imdb_id and concatenate the three fields separated by a : and the rows separated by a ,. The end result should be:

1588970:Actor:Herself, 5690:Director:

Is this possible to do in SQL ?

Upvotes: 1

Views: 43

Answers (1)

David542
David542

Reputation: 110502

You can use a field concat in addition to a GROUP_CONCAT:

SELECT
    GROUP_CONCAT(
        CONCAT (
          person_imdb_id, ':', role_type_id, ':', IFNULL(role_name, '')
        ) SEPARATOR ', '
    )
FROM
    mturk_imdbcredit
GROUP BY
    title_imdb_id

Which gives me:

1588970:Actor:Herself, 5690:Director:

Upvotes: 3

Related Questions