SQL Server拆分关联多值字段生成指定JSON格式求助

你的问题核心是两次CROSS APPLY产生了笛卡尔积，导致每个电话和所有地址都配对，因此需要按拆分后的位置序号关联电话和地址，确保一一对应。以下分两种SQL Server版本给出解决方案：

方案1：SQL Server 2022及以上版本（支持STRING_SPLIT的ordinal参数） #

SQL Server 2022起，STRING_SPLIT新增enable_ordinal参数，可返回拆分元素的位置序号，直接用序号关联即可：

SELECT 
    empid,
    (
        SELECT 
            s_hm.value AS Homenumber,
            s_addr.value AS address
        FROM 
            STRING_SPLIT(e.homenumber, '^', 1) s_hm
        INNER JOIN 
            STRING_SPLIT(e.address, '^', 1) s_addr 
            ON s_hm.ordinal = s_addr.ordinal
        FOR JSON PATH, ROOT('Address')
    ) AS ResultJson
FROM dbo.emp e
WHERE e.empid = 1;

执行后，ResultJson字段会返回预期的JSON结构，其中Homenumber和address按拆分时的位置一一对应。

方案2：SQL Server 2016-2019版本（无ordinal参数） #

旧版本无ordinal支持，需借助XML拆分并生成序号关联：

WITH EmpSplitCTE AS (
    SELECT 
        empid,
        -- 将homenumber转换为XML节点
        CAST('<item>' + REPLACE(homenumber, '^', '</item><item>') + '</item>' AS XML) AS hm_xml,
        -- 将address转换为XML节点
        CAST('<item>' + REPLACE(address, '^', '</item><item>') + '</item>' AS XML) AS addr_xml
    FROM dbo.emp
    WHERE empid = 1
)
SELECT 
    empid,
    (
        SELECT 
            hm_node.value('.', 'NVARCHAR(MAX)') AS Homenumber,
            addr_node.value('.', 'NVARCHAR(MAX)') AS address
        FROM 
            EmpSplitCTE.hm_xml.nodes('/item') AS HM(hm_node)
        INNER JOIN 
            EmpSplitCTE.addr_xml.nodes('/item') AS ADDR(addr_node)
            ON ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FOR JSON PATH, ROOT('Address')
    ) AS ResultJson
FROM EmpSplitCTE;

注意事项 #

• 确保homenumber和address的分隔元素数量一致，否则仅匹配双方都存在的位置，数量少的一方会被截断。
• 若需直接输出JSON而非作为字段，可去掉外层SELECT empid, ...，直接执行子查询部分。

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