SQL技术问询：将分号分隔字符串拆分为列而非行

针对你需要将分号分隔的邮箱从多行拆分转为多列展示的需求，以下是两种可行方案，既满足当前最多3个邮箱的场景，也能灵活兼容更多数量的情况：

方案一：动态SQL（兼容任意数量的邮箱） #

这种方法会自动检测表中最多有多少个邮箱，然后动态生成对应的列，完全不需要手动修改代码来适配更多邮箱的情况。

步骤说明： #

• 先计算每行拆分后的邮箱数量，找到最大值，确定需要生成的列数；
• 动态拼接SQL语句，为每个位置的邮箱生成对应的列（email_1, email_2, ..., email_n）；
• 执行动态生成的SQL，得到最终的多列结果。

代码示例： #

DECLARE @maxEmails INT
DECLARE @sql NVARCHAR(MAX)

-- 第一步：获取表中最大的邮箱数量
SELECT @maxEmails = MAX((LEN(email_address) - LEN(REPLACE(email_address, '; ', ''))) / 2 + 1)
FROM your_table_name; -- 替换成你的实际表名

-- 第二步：动态拼接SQL语句
SET @sql = N'
SELECT id'

DECLARE @i INT = 1
WHILE @i <= @maxEmails
BEGIN
    SET @sql += N', MAX(CASE WHEN rn = ' + CAST(@i AS NVARCHAR) + N' THEN email_new END) AS email_' + CAST(@i AS NVARCHAR)
    SET @i += 1
END

SET @sql += N'
FROM (
    SELECT 
        id,
        email_address,
        Split.a.value(''.'', ''NVARCHAR(max)'') AS email_new,
        ROW_NUMBER() OVER(PARTITION BY id ORDER BY (SELECT NULL)) AS rn
    FROM (
        SELECT 
            id, 
            email_address, 
            CAST(''<M>'' + REPLACE(email_address, ''; '', ''</M><M>'') + ''</M>'' AS XML) AS email_xml
        FROM your_table_name -- 替换成你的实际表名
    ) AS A
    CROSS APPLY email_xml.nodes(''/M'') AS Split(a)
) AS temp
GROUP BY id
ORDER BY id;'

-- 第三步：执行动态生成的SQL
EXEC sp_executesql @sql

方案二：静态SQL（适合固定最大数量的场景） #

如果暂时确定最多不会超过3个邮箱，也可以直接写静态SQL来实现，代码更直观：

SELECT 
    id,
    MAX(CASE WHEN rn = 1 THEN email_new END) AS email_1,
    MAX(CASE WHEN rn = 2 THEN email_new END) AS email_2,
    MAX(CASE WHEN rn = 3 THEN email_new END) AS email_3
FROM (
    SELECT 
        id,
        email_address,
        Split.a.value(''.'', ''NVARCHAR(max)'') AS email_new,
        ROW_NUMBER() OVER(PARTITION BY id ORDER BY (SELECT NULL)) AS rn
    FROM (
        SELECT 
            id, 
            email_address, 
            CAST(''<M>'' + REPLACE(email_address, ''; '', ''</M><M>'') + ''</M>'' AS XML) AS email_xml
        FROM your_table_name -- 替换成你的实际表名
    ) AS A
    CROSS APPLY email_xml.nodes(''/M'') AS Split(a)
) AS temp
GROUP BY id
ORDER BY id;

额外说明： #

• 两种方案都基于你原有的XML拆分逻辑，先拆分出每个邮箱并添加行号（rn），再通过CASE语句结合MAX聚合函数，把同一id下不同行号的邮箱映射到对应列；
• 动态SQL方案会自动适配表中出现的最大邮箱数量，比如后续出现4个邮箱时，会自动生成email_4列，无需修改代码；
• 静态方案如果要扩展列数，只需新增对应的MAX(CASE WHEN rn = N THEN ...)语句即可。

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