如何将具有相同ID的多行数据合并为一行？附数据表示例

Got it, let's figure out how to merge those duplicate ID rows into a single line. Your expected table structure is a bit vague, so I'll cover two common scenarios you're likely aiming for: either combining non-NULL values into a delimited string per column, or pivoting them into separate columns (like A1, A2, C1, C2). Let's break this down for major SQL databases.

Scenario 1: Combine non-NULL values into a delimited string #

This works if you want all values from a column (for the same ID) grouped into a single string (e.g., ID=1's A column becomes 123, 412).

MySQL/MariaDB #

Use the GROUP_CONCAT function, which automatically skips NULL values. Add DISTINCT if you want to remove duplicates (like the two 5s in ID=4's C column):

SELECT
    ID,
    GROUP_CONCAT(DISTINCT A SEPARATOR ', ') AS combined_A,
    GROUP_CONCAT(DISTINCT B SEPARATOR ', ') AS combined_B,
    GROUP_CONCAT(DISTINCT C SEPARATOR ', ') AS combined_C
FROM your_table
GROUP BY ID;

Remove DISTINCT if you want to keep duplicate values (e.g., show 5, 5 for ID=4's C column).

PostgreSQL #

Use STRING_AGG with a FILTER clause to explicitly exclude NULLs:

SELECT
    ID,
    STRING_AGG(A::TEXT, ', ') FILTER (WHERE A IS NOT NULL) AS combined_A,
    STRING_AGG(B::TEXT, ', ') FILTER (WHERE B IS NOT NULL) AS combined_B,
    STRING_AGG(C::TEXT, ', ') FILTER (WHERE C IS NOT NULL) AS combined_C
FROM your_table
GROUP BY ID;

The ::TEXT cast converts non-string data types (like numbers) to text so they can be concatenated.

SQL Server (2017+) #

Use STRING_AGG, which automatically ignores NULL values:

SELECT
    ID,
    STRING_AGG(A, ', ') WITHIN GROUP (ORDER BY (SELECT NULL)) AS combined_A,
    STRING_AGG(B, ', ') WITHIN GROUP (ORDER BY (SELECT NULL)) AS combined_B,
    STRING_AGG(C, ', ') WITHIN GROUP (ORDER BY (SELECT NULL)) AS combined_C
FROM your_table
GROUP BY ID;

Replace (SELECT NULL) with an actual column (like a timestamp) if you need to order the values in the string.

Scenario 2: Pivot values into separate columns #

This works if you want to spread values from multiple rows into new columns (e.g., ID=1's A values go into A1 and A2, B value into B1).

Universal approach (works for most databases with window functions) #

First, assign a row number to each entry per ID, then use conditional aggregation to pivot the rows into columns:

WITH numbered_rows AS (
    SELECT
        ID,
        A,
        B,
        C,
        -- Assign a unique number to each row for the same ID
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL)) AS row_num
    FROM your_table
)
SELECT
    ID,
    -- Grab A values from each row in the ID group
    MAX(CASE WHEN row_num = 1 THEN A END) AS A1,
    MAX(CASE WHEN row_num = 2 THEN A END) AS A2,
    MAX(CASE WHEN row_num = 3 THEN A END) AS A3, -- Add more if you have more rows per ID
    -- Repeat for B and C
    MAX(CASE WHEN row_num = 1 THEN B END) AS B1,
    MAX(CASE WHEN row_num = 2 THEN B END) AS B2,
    MAX(CASE WHEN row_num = 1 THEN C END) AS C1,
    MAX(CASE WHEN row_num = 2 THEN C END) AS C2,
    MAX(CASE WHEN row_num = 3 THEN C END) AS C3
FROM numbered_rows
GROUP BY ID;

The MAX function ignores NULL values, so only the relevant value for each column is kept. If your IDs have more than 3 rows, add additional CASE statements (like A4, B3, etc.).

MySQL 5.x (no window functions) #

If you're on an older MySQL version without CTEs or window functions, use variables to assign row numbers:

SELECT
    ID,
    MAX(CASE WHEN row_num = 1 THEN A END) AS A1,
    MAX(CASE WHEN row_num = 2 THEN A END) AS A2,
    MAX(CASE WHEN row_num = 3 THEN A END) AS A3,
    MAX(CASE WHEN row_num = 1 THEN B END) AS B1,
    MAX(CASE WHEN row_num = 2 THEN B END) AS B2,
    MAX(CASE WHEN row_num = 1 THEN C END) AS C1,
    MAX(CASE WHEN row_num = 2 THEN C END) AS C2,
    MAX(CASE WHEN row_num = 3 THEN C END) AS C3
FROM (
    SELECT
        ID,
        A,
        B,
        C,
        @row_num := IF(@prev_id = ID, @row_num + 1, 1) AS row_num,
        @prev_id := ID
    FROM your_table, (SELECT @row_num := 0, @prev_id := NULL) AS vars
    ORDER BY ID
) AS numbered_rows
GROUP BY ID;

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