SQL Server 2017按lp_id分组8行转多列实现方法咨询

嘿，我来帮你搞定这个多行转多列的需求！根据你的描述，咱们需要把每个licensePlate_id分组下的8行数据，转换成ch_id1到ch_id8、name1到name8这16个列，对吧？

核心思路：先给分组内的行编号，再用条件聚合转列 #

首先，我们需要给每个licensePlate_id分组里的行按x0排序编上序号（1到8），然后通过条件聚合的方式，把每个序号对应的characterName_id和name提取成单独的列。

完整SQL语句 #

SELECT
    cf.capturedFrame_id,
    cf.fileName,
    lp.licensePlate_id,
    -- 提取第1-8行的characterName_id
    MAX(CASE WHEN rn = 1 THEN cn.characterName_id END) AS ch_id1,
    MAX(CASE WHEN rn = 2 THEN cn.characterName_id END) AS ch_id2,
    MAX(CASE WHEN rn = 3 THEN cn.characterName_id END) AS ch_id3,
    MAX(CASE WHEN rn = 4 THEN cn.characterName_id END) AS ch_id4,
    MAX(CASE WHEN rn = 5 THEN cn.characterName_id END) AS ch_id5,
    MAX(CASE WHEN rn = 6 THEN cn.characterName_id END) AS ch_id6,
    MAX(CASE WHEN rn = 7 THEN cn.characterName_id END) AS ch_id7,
    MAX(CASE WHEN rn = 8 THEN cn.characterName_id END) AS ch_id8,
    -- 提取第1-8行的name
    MAX(CASE WHEN rn = 1 THEN cn.name END) AS name1,
    MAX(CASE WHEN rn = 2 THEN cn.name END) AS name2,
    MAX(CASE WHEN rn = 3 THEN cn.name END) AS name3,
    MAX(CASE WHEN rn = 4 THEN cn.name END) AS name4,
    MAX(CASE WHEN rn = 5 THEN cn.name END) AS name5,
    MAX(CASE WHEN rn = 6 THEN cn.name END) AS name6,
    MAX(CASE WHEN rn = 7 THEN cn.name END) AS name7,
    MAX(CASE WHEN rn = 8 THEN cn.name END) AS name8
FROM (
    -- 子查询：给每个licensePlate_id分组内的行按x0排序编号
    SELECT
        cf.capturedFrame_id,
        cf.fileName,
        lp.licensePlate_id,
        cn.characterName_id,
        cn.name,
        ROW_NUMBER() OVER(PARTITION BY lp.licensePlate_id ORDER BY c.x0) AS rn
    FROM CapturedFrame cf
    JOIN LicensePlate lp ON cf.capturedFrame_id = lp.capturedFrame_id
    JOIN Character c ON lp.licensePlate_id = c.licensePlate_id
    JOIN CharacterName cn ON c.characterName_id = cn.characterName_id
) AS grouped_data
GROUP BY cf.capturedFrame_id, cf.fileName, lp.licensePlate_id
ORDER BY lp.licensePlate_id;

关键部分解释 #

1. 行编号（ROW_NUMBER）：ROW_NUMBER() OVER(PARTITION BY lp.licensePlate_id ORDER BY c.x0) 会给每个licensePlate_id下的行按x0从小到大分配1到8的序号，保证和你原查询的排序一致。
2. 条件聚合：用MAX(CASE WHEN rn = N THEN ... END)的方式，每个序号对应一个列。因为每个分组内每个序号只有一行数据，MAX会自动忽略其他行的NULL值，只保留对应序号的有效数据。
3. 分组与排序：外层按capturedFrame_id、fileName、licensePlate_id分组，最后按licensePlate_id排序，和原查询逻辑对齐。

补充说明 #

如果某个licensePlate_id下的行不足8行，对应的列会显示NULL，你可以根据需求用ISNULL函数替换成默认值（比如空字符串），比如ISNULL(MAX(CASE WHEN rn = 1 THEN cn.characterName_id END), 0) AS ch_id1（数值类型用0，字符串用''）。

内容的提问来源于stack exchange，提问作者Babak.Abad