SQL表Unpivoting操作及分号分隔关联值拆分问题

核心问题原因 #

你之前的方案无法得到正确结果，是因为旧版本SQL Server的STRING_SPLIT函数不返回拆分元素的位置序号，多层CROSS APPLY拆分后会生成笛卡尔积，无法匹配三个字段的同位置元素。 #

实现方案 #

方案1：SQL Server 2022 / Azure SQL 版本（推荐） #

该版本的STRING_SPLIT支持enable_ordinal参数，开启后会返回ordinal列表示元素的位置序号，直接按序号关联即可：

SELECT 
    t.Column_A,
    b.value AS Column_B,
    c.value AS Column_C,
    d.value AS Column_D
FROM [TABLE] t
CROSS APPLY STRING_SPLIT(t.Column_B, ';', 1) b
CROSS APPLY STRING_SPLIT(t.Column_C, ';', 1) c
CROSS APPLY STRING_SPLIT(t.Column_D, ';', 1) d
WHERE b.ordinal = c.ordinal 
  AND c.ordinal = d.ordinal
  -- 补充需求：对应位置任意字段为空则跳过
  AND NULLIF(b.value, '') IS NOT NULL
  AND NULLIF(c.value, '') IS NOT NULL
  AND NULLIF(d.value, '') IS NOT NULL

执行后输出和预期完全一致：

方案2：低版本SQL Server 兼容方案 #

如果使用低于2022的SQL Server版本，可以用JSON拆分获取元素位置：

SELECT 
    t.Column_A,
    b.value AS Column_B,
    c.value AS Column_C,
    d.value AS Column_D
FROM [TABLE] t
-- 拆分Column_B，返回位置序号（key从0开始，加1转为1-based序号）
CROSS APPLY OPENJSON(CONCAT('["', REPLACE(t.Column_B, ';', '","'), '"]')) b
-- 拆分Column_C
CROSS APPLY OPENJSON(CONCAT('["', REPLACE(t.Column_C, ';', '","'), '"]')) c
-- 拆分Column_D
CROSS APPLY OPENJSON(CONCAT('["', REPLACE(t.Column_D, ';', '","'), '"]')) d
WHERE CAST(b.[key] AS INT) = CAST(c.[key] AS INT)
  AND CAST(c.[key] AS INT) = CAST(d.[key] AS INT)
  AND NULLIF(b.value, '') IS NOT NULL
  AND NULLIF(c.value, '') IS NOT NULL
  AND NULLIF(d.value, '') IS NOT NULL

注意：如果字段值本身包含双引号，需要先做转义处理再拼接JSON字符串。

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