How to Concatenate Actor Names for Each Movie in a Database?

Concatenate multiple result rows of a single column, grouped by another column

In a database table with column names such as "movie" and "actor", you may need to combine multiple actor rows belonging to the same movie into a single comma-separated string.

<code>表：

| 电影 | 演员 |
|---|---|
| A | 1 |
| A | 2 |
| A | 3 |
| B | 4 |</code>

Expected results:

<code>| 电影 | 演员列表 |
|---|---|
| A | 1, 2, 3 |
| B | 4 |</code>

Solution using string_agg() function:

To do this, you can leverage the string_agg() aggregate function. Here is the query:

<code>SELECT 电影, string_agg(演员, ', ') AS 演员列表
FROM 表
GROUP BY 电影;</code>

The string_agg() function concatenates the values ​​in the "Actor" column, separating them with commas and spaces. The GROUP BY MOVIE clause ensures that the results are grouped by movie name.

Other notes:

1. The 1 in GROUP BY 1 is an index reference to the first column, in this case "Movies".
2. string_agg() expects text data type as input. If your "actor" column has another type (for example, integer), you may need to explicitly convert it to text, such as string_agg(actor::text, ', ').
3. You can use the ORDER BY clause in the string_agg() function to order concatenated values ​​(for example, ORDER BY actors).

This method provides a simple and efficient way to concatenate multiple rows of a specific column and group by another column.

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