如何将关联表的不定多行数据转换为单行列格式（SQL）

核心思路是先给每个ID下的关联记录生成序号，再通过动态透视（Pivot）将多行数据转换为单行多列的格式，适配不定数量的关联记录。以下是主流数据库的具体实现：

MySQL 实现 #

MySQL无原生动态Pivot功能，需通过动态SQL拼接实现：

1. 先给关联记录生成序号

SELECT 
    t1.ID,
    t2.ID2,
    t2.Code,
    t2.Date1,
    ROW_NUMBER() OVER (PARTITION BY t1.ID ORDER BY t2.ID2) AS rn
FROM 表1 t1
JOIN 表2 t2 ON t1.ID2 = t2.ID2

2. 动态生成列并执行透视查询

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
            'MAX(CASE WHEN rn = ', rn, ' THEN ID2 END) AS `ID2-', rn, '`,',
            'MAX(CASE WHEN rn = ', rn, ' THEN Code END) AS `Code-', rn, '`,',
            'MAX(CASE WHEN rn = ', rn, ' THEN Date1 END) AS `Date1-', rn, '`'
        )
    ) INTO @sql
FROM (
    SELECT ROW_NUMBER() OVER (PARTITION BY t1.ID ORDER BY t2.ID2) AS rn
    FROM 表1 t1
    JOIN 表2 t2 ON t1.ID2 = t2.ID2
) AS temp;

SET @sql = CONCAT('SELECT ID, ', @sql, ' FROM (
    SELECT 
        t1.ID,
        t2.ID2,
        t2.Code,
        t2.Date1,
        ROW_NUMBER() OVER (PARTITION BY t1.ID ORDER BY t2.ID2) AS rn
    FROM 表1 t1
    JOIN 表2 t2 ON t1.ID2 = t2.ID2
) AS temp GROUP BY ID');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQL Server 实现 #

利用原生动态PIVOT功能：

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);

-- 生成所有需要的列名
SELECT @cols = STRING_AGG(
    CONCAT('[ID2-', rn, '], [Code-', rn, '], [Date1-', rn, ']'),
    ','
)
FROM (
    SELECT DISTINCT ROW_NUMBER() OVER (PARTITION BY t1.ID ORDER BY t2.ID2) AS rn
    FROM 表1 t1
    JOIN 表2 t2 ON t1.ID2 = t2.ID2
) AS temp;

-- 拼接并执行动态查询
SET @query = CONCAT('
SELECT ID, ', @cols, '
FROM (
    SELECT 
        t1.ID,
        CONCAT(''ID2-'', ROW_NUMBER() OVER (PARTITION BY t1.ID ORDER BY t2.ID2)) AS col_ID2,
        CONCAT(''Code-'', ROW_NUMBER() OVER (PARTITION BY t1.ID ORDER BY t2.ID2)) AS col_Code,
        CONCAT(''Date1-'', ROW_NUMBER() OVER (PARTITION BY t1.ID ORDER BY t2.ID2)) AS col_Date1,
        CAST(t2.ID2 AS NVARCHAR(MAX)) AS val_ID2,
        t2.Code AS val_Code,
        CONVERT(NVARCHAR(MAX), t2.Date1, 23) AS val_Date1
    FROM 表1 t1
    JOIN 表2 t2 ON t1.ID2 = t2.ID2
) AS src
PIVOT (MAX(val_ID2) FOR col_ID2 IN (', STRING_AGG(CONCAT('[ID2-', rn, ']'), ','), ')) AS pvt1
PIVOT (MAX(val_Code) FOR col_Code IN (', STRING_AGG(CONCAT('[Code-', rn, ']'), ','), ')) AS pvt2
PIVOT (MAX(val_Date1) FOR col_Date1 IN (', STRING_AGG(CONCAT('[Date1-', rn, ']'), ','), ')) AS pvt3
');

EXEC sp_executesql @query;

PostgreSQL 实现 #

通过动态SQL结合CASE语句实现：

WITH numbered_records AS (
    SELECT 
        t1.ID,
        t2.ID2,
        t2.Code,
        t2.Date1,
        ROW_NUMBER() OVER (PARTITION BY t1.ID ORDER BY t2.ID2) AS rn
    FROM 表1 t1
    JOIN 表2 t2 ON t1.ID2 = t2.ID2
),
max_rn AS (SELECT MAX(rn) FROM numbered_records)
SELECT string_agg(
    CONCAT(
        'MAX(CASE WHEN rn = ', rn, ' THEN ID2 END) AS "ID2-', rn, '",',
        'MAX(CASE WHEN rn = ', rn, ' THEN Code END) AS "Code-', rn, '",',
        'MAX(CASE WHEN rn = ', rn, ' THEN Date1 END) AS "Date1-', rn, '"'
    ),
    ','
) INTO v_cols
FROM generate_series(1, (SELECT * FROM max_rn)) AS rn;

EXECUTE format('
SELECT ID, %s
FROM numbered_records
GROUP BY ID
', v_cols);

注意事项 #

• 代码中的ORDER BY t2.ID2可根据实际需求调整排序规则（如按Date1排序）
• 若表2包含更多字段，只需在CASE语句中追加对应字段的转换逻辑（MAX(CASE WHEN rn = n THEN 字段名 END) AS 字段名-n）
• 动态SQL会自动适配每个ID下的最大关联记录数，无需提前指定列数

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