Oracle SQL合并同col1、col2分组的col3字段值

Original Table Structure #

+---------------------------+
| col1 | col2 | col3        |
+--------+---------+--------+
| 1    | A     | apple      |
| 1    | A     | banana     |
| 2    | B     | grapes     |
| 2    | A     | orange     |
+--------+---------+--------+

Requirement #

We need to merge the col3 values into a single comma-separated string for all records where col1 and col2 have identical values.

Expected Output #

+----------------------------------+
| col1 | col2 | col3               |
+--------+---------+---------------+
| 1    | A     | apple, banana     |
| 2    | B     | grapes            |
| 2    | A     | orange            |
+--------+---------+---------------+

Solution SQL Query #

Here's the Oracle SQL statement that achieves this using the built-in LISTAGG function, which is purpose-built for string aggregation within grouped rows:

SELECT 
    col1,
    col2,
    LISTAGG(col3, ', ') WITHIN GROUP (ORDER BY col3) AS col3
FROM 
    your_table_name
GROUP BY 
    col1, col2;

Quick Breakdown:

• LISTAGG(col3, ', '): Concatenates all col3 values in each group, using a comma + space as the separator.
• WITHIN GROUP (ORDER BY col3): Ensures the merged values are sorted alphabetically (adjust the ORDER BY clause if you need a different sequence, like matching the original row order).
• GROUP BY col1, col2: Groups rows by the combination of col1 and col2, so we only merge values for matching pairs of these two columns.

Just replace your_table_name with the actual name of your table, and this query will generate the desired result.

内容的提问来源于stack exchange，提问作者Miracle