How to Concatenate Actors for Each Movie in a SQL Table?

Combining Actor Names for Each Movie in a SQL Table

Imagine a table with "Movie" and "Actor" columns. The task is to retrieve a list of movies and their corresponding actors, with all actors for each movie combined into a single string.

Solution using string_agg():

The most efficient method uses the string_agg() function (available in PostgreSQL 9.0 and later):

<code class="language-sql">SELECT movie, string_agg(actor, ', ') AS actor_list
FROM table_name
GROUP BY movie;</code>

string_agg() concatenates actor names, separated by commas, for each movie. GROUP BY movie ensures one row per film.

Alternative for Older PostgreSQL Versions (pre-9.0):

For older PostgreSQL versions, use array_agg() and array_to_string():

<code class="language-sql">SELECT movie, array_to_string(array_agg(actor ORDER BY actor), ', ') AS actor_list
FROM table_name
GROUP BY movie;</code>

This aggregates actors into an array, then converts it to a comma-separated string. The ORDER BY actor clause sorts actors alphabetically within each movie's list.

Customizing the Actor List Order:

To control the order of actors within each list (e.g., by billing order), add ORDER BY inside string_agg() or array_agg():

<code class="language-sql">SELECT movie, string_agg(actor, ', ' ORDER BY actor) AS actor_list  --Alphabetical order
FROM table_name
GROUP BY movie;</code>

This approach streamlines the presentation of movie-actor relationships, facilitating data analysis and display.

The above is the detailed content of How to Concatenate Actors for Each Movie in a SQL Table?. For more information, please follow other related articles on the PHP Chinese website!