如何用SQL实现其他列值相同时合并指定列的值？

Great question! This is a super common scenario when you need to collapse duplicate records (based on all columns except one) into a single row, with the unique values of that one column combined into a single string. Let's walk through exactly how to do this, with examples for the most popular SQL databases.

First, Let's Clarify with Sample Data #

Let's assume your raw dataset looks something like this (matching your description of 6 rows where the first 4 collapse into 2 unique records when excluding Type):

Raw Data:

ID	Name	Type
1	Alice	Admin
2	Alice	User
3	Bob	Editor
4	Bob	Viewer
5	Charlie	Guest
6	Dave	Owner

Your desired output would group rows where all columns except Type are identical, and combine the Type values:

Expected Result:

Name	Combined_Type
Alice	Admin, User
Bob	Editor, Viewer
Charlie	Guest
Dave	Owner

The Solution: String Aggregation Functions #

The core idea is to GROUP BY all columns except the one you want to combine, then use a database-specific aggregation function to concatenate the values of the target column (Type). Here's how to implement this in major SQL dialects:

1. MySQL / MariaDB

Use GROUP_CONCAT():

SELECT
  -- Include ALL columns you want to keep as unique (exclude Type)
  Name,
  -- Combine Type values, add a separator, and optionally remove duplicates
  GROUP_CONCAT(DISTINCT Type SEPARATOR ', ') AS Combined_Type
FROM your_table
-- Group by the same columns you selected (excluding the aggregated Type)
GROUP BY Name;

• Remove DISTINCT if you want to keep duplicate Type values (e.g., if Alice had two "Admin" entries, they'd both show up).
• Adjust SEPARATOR to use a different delimiter (like ' | ' or ';') if needed.

2. SQL Server

Use STRING_AGG() with WITHIN GROUP to control ordering:

SELECT
  Name,
  STRING_AGG(DISTINCT Type, ', ') WITHIN GROUP (ORDER BY Type) AS Combined_Type
FROM your_table
GROUP BY Name;

• The ORDER BY Type clause ensures your combined values are sorted alphabetically (remove it if order doesn't matter).

3. PostgreSQL

PostgreSQL's STRING_AGG() has a simpler syntax for ordering:

SELECT
  Name,
  STRING_AGG(DISTINCT Type, ', ' ORDER BY Type) AS Combined_Type
FROM your_table
GROUP BY Name;

4. Oracle

Use LISTAGG() (available in Oracle 11g+; DISTINCT requires Oracle 12c+):

SELECT
  Name,
  LISTAGG(DISTINCT Type, ', ') WITHIN GROUP (ORDER BY Type) AS Combined_Type
FROM your_table
GROUP BY Name;

• If you're on an older Oracle version (pre-12c), you'll need to first deduplicate rows with a subquery before using LISTAGG().

Key Notes to Remember #

• Grouping Columns: Make sure your GROUP BY clause includes every column you're selecting except the aggregated Type column. This ensures you only group rows where all other values are identical.
• Duplicates: Use DISTINCT inside the aggregation function if you want to avoid repeating the same Type value in the combined string.
• Order: Adding an ORDER BY inside the aggregation function lets you control the order of the combined values (e.g., alphabetical or chronological).

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