如何在SQL中将源表数据转换为两列多行的目标结构？

需求 #

基于源表每行数据，生成多行目标数据：每组编码对应目标表的一行并标记分组序号，无编码时生成特定行。

数据样例 #

源表结构 #

目标表预期结果 #

优化后的SQL实现 #

原SQL存在逻辑冗余、语法错误（如第二个WHERE子句未写完），以下是更简洁准确的方案：

WITH CodeGroups AS (
    -- 处理第一组编码（COL_1_CD、COL_1_CD_1），seq标记为1
    SELECT ID, CODES, 1 AS seq
    FROM TBL
    UNPIVOT (
        CODES FOR CPT_CDD IN (COL_1_CD, COL_1_CD_1)
    ) AS p
    WHERE CODES IS NOT NULL

    UNION ALL

    -- 处理第二组编码（COL_2_CD、COL_2_CD_1），seq标记为2
    SELECT ID, CODES, 2 AS seq
    FROM TBL
    UNPIVOT (
        CODES FOR CPT_CDD IN (COL_2_CD, COL_2_CD_1)
    ) AS p
    WHERE CODES IS NOT NULL

    UNION ALL

    -- 所有编码为空时，生成seq=3的行
    SELECT ID, NULL AS CODES, 3 AS seq
    FROM TBL
    WHERE COL_1_CD IS NULL 
      AND COL_1_CD_1 IS NULL 
      AND COL_2_CD IS NULL 
      AND COL_2_CD_1 IS NULL
)
SELECT ID, CODES, seq
FROM CodeGroups
ORDER BY ID, seq;

关键逻辑说明 #

• 用UNION ALL替代UNION，避免无意义的去重，提升查询效率
• 每组编码单独做UNPIVOT后过滤非空值，自动适配单值/双值的场景，无需额外判断组合条件
• 单独补充全空场景，确保覆盖所有边界情况
• 最终按ID和seq排序，保证结果顺序符合预期

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