SQL Server如何将details表多行合并至main表单行实现列转行

现有两张SQL Server表，样例数据如下：

main表 #

m_id  eID  sDate       eDate
1     75   2022-12-01  NULL

details表 #

m_id  cc_id  cu_id  perc
1     1      1      40
1     1      2      40
1     1      3      20

需要实现将details表中同一m_id的多行数据，合并到main表对应行的单行中，输出格式如下：

m_id  eID  sDate       eDate  cc_id^1  cu_id^1  perc^1  cc_id^2  cu_id^2  perc^2  cc_id^3  cu_id^3  perc^3
1     75   2022-12-01  NULL   1        1        40      1        2        40      1        3        20

尝试过PIVOT函数，但发现它会将列的唯一值作为新列头并统计次数（比如针对perc列的40生成列40，值为2），不符合需求。

固定行数（已知最多3条记录） #

如果每个m_id对应的details记录数固定，可通过条件聚合实现：

SELECT 
    m.m_id,
    m.eID,
    m.sDate,
    m.eDate,
    MAX(CASE WHEN rn = 1 THEN d.cc_id END) AS [cc_id^1],
    MAX(CASE WHEN rn = 1 THEN d.cu_id END) AS [cu_id^1],
    MAX(CASE WHEN rn = 1 THEN d.perc END) AS [perc^1],
    MAX(CASE WHEN rn = 2 THEN d.cc_id END) AS [cc_id^2],
    MAX(CASE WHEN rn = 2 THEN d.cu_id END) AS [cu_id^2],
    MAX(CASE WHEN rn = 2 THEN d.perc END) AS [perc^2],
    MAX(CASE WHEN rn = 3 THEN d.cc_id END) AS [cc_id^3],
    MAX(CASE WHEN rn = 3 THEN d.cu_id END) AS [cu_id^3],
    MAX(CASE WHEN rn = 3 THEN d.perc END) AS [perc^3]
FROM main m
LEFT JOIN (
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY m_id ORDER BY (SELECT NULL)) AS rn
    FROM details
) d ON m.m_id = d.m_id
GROUP BY m.m_id, m.eID, m.sDate, m.eDate;

动态行数（记录数不固定） #

如果每个m_id对应的details记录数不确定，需用动态SQL自动生成对应列：

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

-- 自动生成列名及对应聚合逻辑
SELECT @cols = STRING_AGG(
    CONCAT(
        'MAX(CASE WHEN rn = ', rn, ' THEN cc_id END) AS [cc_id^', rn, '],',
        'MAX(CASE WHEN rn = ', rn, ' THEN cu_id END) AS [cu_id^', rn, '],',
        'MAX(CASE WHEN rn = ', rn, ' THEN perc END) AS [perc^', rn, ']'
    ), ','
)
FROM (
    SELECT DISTINCT ROW_NUMBER() OVER(PARTITION BY m_id ORDER BY (SELECT NULL)) AS rn
    FROM details
) t;

-- 拼接完整SQL语句
SET @sql = CONCAT(
    'SELECT m.m_id, m.eID, m.sDate, m.eDate, ', @cols, ' ',
    'FROM main m ',
    'LEFT JOIN (',
        'SELECT *, ROW_NUMBER() OVER(PARTITION BY m_id ORDER BY (SELECT NULL)) AS rn ',
        'FROM details',
    ') d ON m.m_id = d.m_id ',
    'GROUP BY m.m_id, m.eID, m.sDate, m.eDate;'
);

-- 执行动态SQL
EXEC sp_executesql @sql;

核心逻辑说明 #

1. 先用ROW_NUMBER()给每个m_id下的details记录分配唯一序号rn，区分同组内的不同行；
2. 条件聚合通过CASE WHEN匹配序号，提取对应列的值，再用MAX（或MIN，每组序号仅对应一个值）聚合得到单行结果；
3. 动态SQL会根据实际记录数自动生成对应数量的列，无需手动调整。

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