How Can I Combine Multiple Rows of Data into a Single Row Using SQL's GROUP BY Clause?

Use GROUP BY to merge multiple rows of data into one row

During data processing, you may encounter tables where each row represents a unique entity, and certain columns need to be consolidated across these entities. This happens when you need to join multiple result rows for a specific column into one row.

Suppose there is a table called "ActorsInfo" with two columns: "Movie" and "Actor". Each row represents a combination of movies and their associated actors. Your goal is to transform this data into a new table that contains a "Movie" column and an "ActorList" column that contains a comma-separated list of all actors in each movie.

For this, SQL’s GROUP BY clause comes in handy. GROUP BY allows you to group rows based on the values ​​of one or more columns and then apply aggregate functions to calculate summary information within each group.

The key here is the string_agg() aggregate function. It concatenates values ​​from specified columns, separated by a delimiter of your choice. In this case, the syntax is:

<code class="language-sql">SELECT Movie, string_agg(Actor, ', ') AS ActorList
FROM ActorsInfo
GROUP BY Movie;</code>

Group the rows by movie title by specifying "Movie" in both the SELECT and GROUP BY clauses, and calculate the ActorList column by concatenating the Actor values ​​separated by commas.

The result is a new table where each row represents a unique movie and the "ActorList" column contains the complete list of all actors involved in that movie.

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