How Can I Combine Multiple Actor Names into a Single Column per Movie in SQL?

Consolidating Multiple Actor Names into a Single Column per Film in SQL

Working with movie databases often requires summarizing actor information for each film. This involves merging multiple actor names associated with a single movie into a single column entry.

SQL Solution

PostgreSQL provides the string_agg() function, ideal for this task. Here's a query demonstrating its use:

SELECT
    movie,
    string_agg(actor, ', ') AS actors
FROM
    tbl
GROUP BY
    movie;

This query:

• movie: Specifies the column holding movie titles.
• actor: Specifies the column listing actor names.
• string_agg(actor, ', '): This function concatenates actor names for each movie, separating them with commas and spaces.
• GROUP BY movie: Groups the results, ensuring one row per movie.

Detailed Explanation

The string_agg() function excels at combining multiple rows into a single string value. Here, it creates a comma-separated list of actors for each film.

For sorted actor lists, include an ORDER BY clause within string_agg():

string_agg(actor, ', ' ORDER BY actor) AS actors

However, a subquery often offers better performance for sorting.

Advantages of string_agg()

• Streamlines data processing by aggregating multiple rows into single values.
• Improves data presentation by grouping related actor data for each movie.

The above is the detailed content of How Can I Combine Multiple Actor Names into a Single Column per Movie in SQL?. For more information, please follow other related articles on the PHP Chinese website!