How to Concatenate Columns Based on GROUP BY in SQL?

Column connection under GROUP BY statement in SQL

In SQL, a common way to combine or join columns based on a GROUP BY statement is to use aggregate functions in conjunction with string operators. Let's explore how to achieve this.

Consider the following example table:

ID	用户	活动	页面URL
1	我	act1	ab
2	我	act1	cd
3	你	act2	xy
4	你	act2	st

To concatenate the PageURL values ​​for each user and activity group separated by commas, we can use the following query:

SELECT [user], activity, STUFF((SELECT ',' page URL FROM table name WHERE [user] = a. [user] AND activity = a. activity FOR XML PATH ('')) , 1, 1, '') AS URL list FROM table name AS a GROUP BY [user], activity

Query breakdown:

• SELECT [User], Activity, URL List: Select the User, Activity, and URL List columns to display.
• STUFF: Concatenates PageURL values ​​into a single string.
• (SELECT ',' Page URL ...): Subquery to extract the PageURL value of each group and concatenate them with comma as separator.
• FOR XML PATH (''): Converts the result of the subquery to an XML string.
• STUFF(... , 1, 1, ''): Removes the comma at the beginning of the XML string, leaving only the concatenated value.
• GROUP BY [USER], ACTIVITY: Groups the results by "User" and "Activity" so that the join is performed for each unique combination.

The generated table will have the desired layout:

用户	活动	URL列表
我	act1	ab, cd
你	act2	xy, st

This approach leverages the power of aggregate functions and string operations to effectively concatenate column values ​​based on GROUP BY operations, making it a flexible solution for a variety of data manipulation tasks.

The above is the detailed content of How to Concatenate Columns Based on GROUP BY in SQL?. For more information, please follow other related articles on the PHP Chinese website!