如何将SQL查询单条结果按额外许可证数量拆分为多行？

问题背景 #

执行以下查询：

SELECT
        [RecordID]
    ,   [PermitType]
    ,   [Additional Permits]
    ,   [License_1]
    ,   [License_2]                 
FROM Permits 
WHERE
    [Permit Type] = 'Additional or Replacement'
    [RecordID] = 'P2023-10669' -- 注意：原查询此处缺少AND，会导致语法错误

得到结果：

RecordID    PermitType  Additional Permits  License_1   License_2
P2023-10669 Replacement 2                   Rh3184      Aez8532

需要将结果按Additional Permits的数值拆分，让每个许可证单独占一行，预期输出：

RecordID    PermitType  Additional Permits  License_1   License_2
P2023-10669 Replacement 2                   Rh3184      
P2023-10669 Replacement 2                               Aez8532

实现方法 #

方法1：UNION ALL 拆分列 #

适合许可证列数量固定的场景，逻辑直接易懂：

SELECT
    [RecordID],
    [PermitType],
    [Additional Permits],
    [License_1] AS License_1,
    NULL AS License_2
FROM Permits
WHERE
    [Permit Type] = 'Additional or Replacement'
    AND [RecordID] = 'P2023-10669'
    AND [License_1] IS NOT NULL
UNION ALL
SELECT
    [RecordID],
    [PermitType],
    [Additional Permits],
    NULL AS License_1,
    [License_2] AS License_2
FROM Permits
WHERE
    [Permit Type] = 'Additional or Replacement'
    AND [RecordID] = 'P2023-10669'
    AND [License_2] IS NOT NULL;

通过两次查询分别返回License_1和License_2的独立行，用UNION ALL合并结果，同时过滤空许可证避免无效行。

方法2：UNPIVOT 动态转换列到行 #

如果后续可能新增License_3、License_4等列，这种方法扩展性更好：

SELECT
    [RecordID],
    [PermitType],
    [Additional Permits],
    CASE WHEN LicenseCol = 'License_1' THEN LicenseValue ELSE NULL END AS License_1,
    CASE WHEN LicenseCol = 'License_2' THEN LicenseValue ELSE NULL END AS License_2
FROM (
    SELECT
        [RecordID],
        [PermitType],
        [Additional Permits],
        LicenseCol,
        LicenseValue
    FROM Permits
    UNPIVOT (
        LicenseValue FOR LicenseCol IN ([License_1], [License_2])
    ) AS Unpvt
    WHERE
        [Permit Type] = 'Additional or Replacement'
        AND [RecordID] = 'P2023-10669'
        AND LicenseValue IS NOT NULL
) AS Pivoted;

先通过UNPIVOT把多个License列转换成LicenseCol（列名）和LicenseValue（许可证值）的行数据，再用CASE语句将值映射回原列结构，确保每行只有一个许可证值非空。

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