求助：SQL实现多分隔符字符串拆分多行多列的动态查询（无函数）

我来帮你搞定这个字符串拆分的问题！你想要把类似'John, Mark, Peter|23, 32, 45'这样的字符串拆分成姓名和年龄一一对应的行，还要动态处理任意数量的记录，而且尽量不用自定义函数对吧？之前试Cross Apply没成功，大概率是没处理好两部分拆分后的顺序关联，我给你两个可行的方案，分别适配不同的SQL Server版本：

方案一：适用于SQL Server 2022及以上版本（推荐） #

SQL Server 2022开始，STRING_SPLIT函数新增了ordinal参数，能保证拆分后的结果和原字符串中的顺序完全一致，这就完美解决了姓名和年龄的对应问题。我们可以先把主字符串按|拆分成姓名列表和年龄列表，再分别拆分这两个列表，最后通过序号关联：

DECLARE @input NVARCHAR(MAX) = 'John, Mark, Peter|23, 32, 45';

WITH main_parts AS (
    -- 拆分主字符串为姓名串和年龄串
    SELECT 
        MAX(CASE WHEN ordinal = 1 THEN value END) AS name_str,
        MAX(CASE WHEN ordinal = 2 THEN value END) AS age_str
    FROM STRING_SPLIT(@input, '|', 1) -- 第三个参数1启用ordinal返回
)
SELECT 
    TRIM(n.value) AS Name, -- TRIM去掉每个值前后的空格
    TRIM(a.value) AS Age
FROM main_parts
-- 拆分姓名串为单个姓名，保留序号
CROSS APPLY STRING_SPLIT(name_str, ',', 1) n
-- 拆分年龄串为单个年龄，保留序号
CROSS APPLY STRING_SPLIT(age_str, ',', 1) a
-- 通过序号匹配姓名和年龄
WHERE n.ordinal = a.ordinal;

执行后会得到：

方案二：适用于SQL Server 2017及以下版本 #

如果你的SQL Server版本不支持带ordinal的STRING_SPLIT，可以用XML拆分法配合行号来保证顺序，同样不需要自定义函数：

DECLARE @input NVARCHAR(MAX) = 'John, Mark, Peter|23, 32, 45';

WITH main_parts AS (
    -- 先拆分主字符串为姓名串和年龄串
    SELECT 
        LEFT(@input, CHARINDEX('|', @input) - 1) AS name_str,
        RIGHT(@input, LEN(@input) - CHARINDEX('|', @input)) AS age_str
),
name_split AS (
    -- 拆分姓名串，生成带行号的姓名列表
    SELECT 
        TRIM(Split.a.value('.', 'NVARCHAR(100)')) AS Name,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ordinal
    FROM (
        SELECT CAST('<item>' + REPLACE(name_str, ',', '</item><item>') + '</item>' AS XML) AS Data
        FROM main_parts
    ) AS A
    CROSS APPLY Data.nodes('/item') AS Split(a)
),
age_split AS (
    -- 拆分年龄串，生成带行号的年龄列表
    SELECT 
        TRIM(Split.a.value('.', 'NVARCHAR(100)')) AS Age,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ordinal
    FROM (
        SELECT CAST('<item>' + REPLACE(age_str, ',', '</item><item>') + '</item>' AS XML) AS Data
        FROM main_parts
    ) AS A
    CROSS APPLY Data.nodes('/item') AS Split(a)
)
-- 通过行号关联姓名和年龄
SELECT ns.Name, aspl.Age
FROM name_split ns
JOIN age_split aspl ON ns.ordinal = aspl.ordinal;

这个方案的核心是用XML把逗号分隔的字符串转成节点集合，再通过ROW_NUMBER()生成顺序号，确保姓名和年龄的位置一一对应。

为什么你之前的Cross Apply没成功？ #

大概率是没处理好拆分后的顺序问题——如果拆分姓名和年龄时没有保留原顺序，直接关联就会出现匹配错误。上面的两个方案都重点保证了顺序的一致性，这是解决问题的关键。

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