How to Concatenate Column Values with GROUP BY in SQL?

Use SQL’s GROUP BY statement to join column values

Suppose you have a table containing the following data:

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

You want to group the data by User and Activity and combine the values ​​from the Page URL column in each group into a single comma-separated String. The desired output is as follows:

<code>用户 | 活动 | 页面URL
我 | act1 | ab, cd
你 | act2 | xy, st</code>

To achieve this join using a GROUP BY query, you can use the STUFF() function along with a subquery in the GROUP BY clause. The following is a detailed breakdown:

<code class="language-sql">SELECT
    [用户], 活动,
    STUFF(
        (SELECT DISTINCT ',' + 页面URL
         FROM 表名称
         WHERE [用户] = a.[用户] AND 活动 = a.活动
         FOR XML PATH (''))
         , 1, 1, '')  AS URL列表
FROM 表名称 AS a
GROUP BY [用户], 活动</code>

• SELECT: Select the required columns: User, Activity and connected Page URL values ​​as a list of URLs .
• STUFF(): This function accepts three parameters: the string to be modified, the starting position, the number of characters to be deleted, and the string to be inserted. In this case, it removes the first character (comma) and inserts the concatenated Page URL value into the modified string.
Subquery in
• FOR XML PATH(''): This subquery selects the unique User and Activity 🎜>Page URL value. It uses the FOR XML PATH('') clause to return a single string by concatenating the values.
• a.[User] AND activity = a.Activity: This section ensures that the subquery only retrieves Page URL values ​​that belong to the group currently being processed.
• GROUP BY: Groups the data by User and Activity to aggregate the Page URL value for each group.

By executing this query you will get the desired output where the

Page URL values ​​for each unique User and Activity combination are merged in together and separated by commas.

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