如何转换不同粒度的Detail表并与Main表关联得到目标结果？

要实现将Main表（单Id粒度）与Detail表（Id+Number粒度）关联，并把Detail的多行数据转成Main每行对应的横向多列，核心思路是先给Detail表的行按Id分组编号，再通过条件聚合将行转列，以下是具体实现方案：

方案一：条件聚合（通用兼容所有主流SQL数据库） #

这种方法兼容性最强，适用于MySQL、PostgreSQL、SQL Server、Oracle等所有支持窗口函数的数据库。

步骤1：给Detail表的行按Id分组编号 #

用ROW_NUMBER()窗口函数，给每个Id下的Detail数据按Number排序生成行号，标记为第1行、第2行等：

WITH Detail_Ranked AS (
    SELECT 
        Id,
        Col_C,
        Col_D,
        -- 按Id分组，Number升序生成行号
        ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Number) AS Row_Num
    FROM Detail
)

步骤2：关联Main表并转置列 #

通过LEFT JOIN关联Main表，再用MAX(CASE...)的条件聚合方式，把每个行号对应的Col_C和Col_D转成单独的列：

SELECT 
    m.Id,
    m.Name,
    m.Col_A,
    m.Col_B,
    -- 提取第1行的Col_C和Col_D
    MAX(CASE WHEN dr.Row_Num = 1 THEN dr.Col_C END) AS Col_C_Line1,
    MAX(CASE WHEN dr.Row_Num = 1 THEN dr.Col_D END) AS Col_D_Line1,
    -- 提取第2行的Col_C和Col_D
    MAX(CASE WHEN dr.Row_Num = 2 THEN dr.Col_C END) AS Col_C_Line2,
    MAX(CASE WHEN dr.Row_Num = 2 THEN dr.Col_D END) AS Col_D_Line2,
    -- 提取第3行的Col_C和Col_D（如果需要更多行，继续添加对应语句）
    MAX(CASE WHEN dr.Row_Num = 3 THEN dr.Col_C END) AS Col_C_Line3,
    MAX(CASE WHEN dr.Row_Num = 3 THEN dr.Col_D END) AS Col_D_Line3
FROM MAIN m
LEFT JOIN Detail_Ranked dr ON m.Id = dr.Id
-- 按Main表的主键分组，确保每个Id只返回一行
GROUP BY m.Id, m.Name, m.Col_A, m.Col_B
ORDER BY m.Id;

逻辑说明 #

• LEFT JOIN保证Main表的所有行都会被保留，即使对应Id没有Detail数据（结果中对应列会显示NULL）。
• MAX(CASE...)的作用是：在GROUP BY之后，每个Id只有一行，CASE语句会取出对应行号的有效值，其他行的CASE结果为NULL，MAX函数会自动忽略NULL，最终得到该行号对应的列值。

方案二：使用PIVOT语法（适用于支持PIVOT的数据库） #

如果使用SQL Server、Oracle等支持PIVOT语法的数据库，也可以通过UNPIVOT+PIVOT的方式实现，但语法兼容性较差：

WITH Detail_Ranked AS (
    SELECT 
        Id,
        'Col_C_Line' + CAST(ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Number) AS VARCHAR) AS Col_C_Name,
        Col_C,
        'Col_D_Line' + CAST(ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Number) AS VARCHAR) AS Col_D_Name,
        Col_D
    FROM Detail
),
Detail_Unpivot AS (
    -- 先把Col_C和Col_D转成键值对格式
    SELECT Id, Col_Name, Col_Value
    FROM Detail_Ranked
    UNPIVOT (
        Col_Value FOR Col_Name IN (Col_C, Col_D)
    ) u
),
Detail_Pivot AS (
    -- 再把键值对转成宽表
    SELECT Id, [Col_C_Line1], [Col_D_Line1], [Col_C_Line2], [Col_D_Line2], [Col_C_Line3], [Col_D_Line3]
    FROM Detail_Unpivot
    PIVOT (
        MAX(Col_Value) FOR Col_Name IN ([Col_C_Line1], [Col_D_Line1], [Col_C_Line2], [Col_D_Line2], [Col_C_Line3], [Col_D_Line3])
    ) p
)
-- 关联Main表得到最终结果
SELECT 
    m.Id,
    m.Name,
    m.Col_A,
    m.Col_B,
    dp.[Col_C_Line1],
    dp.[Col_D_Line1],
    dp.[Col_C_Line2],
    dp.[Col_D_Line2],
    dp.[Col_C_Line3],
    dp.[Col_D_Line3]
FROM MAIN m
LEFT JOIN Detail_Pivot dp ON m.Id = dp.Id
ORDER BY m.Id;

注意事项 #

• 如果Detail表中同一Id的行数不固定，需要动态生成列的话，不同数据库的实现方式不同：比如MySQL需要用存储过程拼接SQL，SQL Server可以用动态SQL。
• 如果行数可以预估，直接写固定的CASE语句是最简单高效的方式。

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