How Can I Concatenate Multiple Rows into a Single Row Using SQL?

Combining Multiple Rows into a Single Row with SQL

Many database tasks require merging multiple rows with identical values in one column into a single row, concatenating the values from another column. Imagine a movie database with actors listed in separate rows for each film:

<code>Movie | Actor
----------------
A      1
A      2
A      3
B      4</code>

The goal is to create a result set like this:

<code>Movie | ActorList
----------------
A      1, 2, 3
B      4</code>

Using String Aggregation for Consolidation

SQL's string_agg function provides an efficient solution. This aggregate function concatenates values from a specified column. Here's the query:

SELECT movie, string_agg(actor::text, ', ') AS actor_list
FROM tbl
GROUP BY movie;

Here's the breakdown:

1. string_agg(actor::text, ', '): This part concatenates the actor column values, using a comma and space as the separator. The ::text explicitly casts the actor column to text, which is necessary if it's not already a text data type.
2. GROUP BY movie: This groups the rows by the movie column, ensuring that actors for the same movie are aggregated together.
3. actor_list: This is the alias for the resulting concatenated string.

Enhancements and Considerations:

• Data Type: Ensure your column being aggregated is a text type. If not, use an explicit cast (like ::text shown above).
• Ordering: To sort the actors within each list, add ORDER BY inside string_agg:

SELECT movie, string_agg(actor::text, ', ' ORDER BY actor) AS actor_list
FROM tbl
GROUP BY movie;

This in-query sorting is generally more efficient than sorting the final result set.

This approach effectively consolidates multiple rows into a single, more manageable row, making data analysis and reporting simpler.

The above is the detailed content of How Can I Concatenate Multiple Rows into a Single Row Using SQL?. For more information, please follow other related articles on the PHP Chinese website!