SQL Server拆分多值列并匹配多列数据为单列的实现方法

针对你的需求，这里提供两种实用的SQL实现方式，核心思路是给拆分后的Text值和Loc列分别标记序号，再通过序号关联实现一一对应。

方法一：通用版本（兼容SQL Server 2016+） #

先给拆分后的Text值生成序号，再将Loc1-Loc4列转换为行格式并标记对应序号，最后通过Name和序号关联两者：

WITH SplitText AS (
    SELECT 
        Name,
        value AS TextValue,
        -- 按Name分组生成拆分值的序号
        ROW_NUMBER() OVER (PARTITION BY Name ORDER BY (SELECT NULL)) AS Seq
    FROM YourTable
    CROSS APPLY STRING_SPLIT([Text], ';')
),
UnpivotedLoc AS (
    SELECT 
        Name,
        LocValue,
        -- 给每个Loc列分配对应序号
        CASE LocCols 
            WHEN 'Loc1' THEN 1 
            WHEN 'Loc2' THEN 2 
            WHEN 'Loc3' THEN 3 
            WHEN 'Loc4' THEN 4 
        END AS Seq
    FROM YourTable
    UNPIVOT (
        LocValue FOR LocCols IN (Loc1, Loc2, Loc3, Loc4)
    ) AS u
    -- 过滤空的Loc值（按需调整）
    WHERE LocValue IS NOT NULL AND LocValue <> ''
)
SELECT 
    st.Name,
    st.TextValue,
    ul.LocValue
FROM SplitText st
INNER JOIN UnpivotedLoc ul 
    ON st.Name = ul.Name 
    AND st.Seq = ul.Seq;

注意：STRING_SPLIT在SQL Server 2016-2019版本中不保证拆分顺序与原字符串一致，如果你的业务要求严格对应原Text中的顺序，建议使用下面的SQL Server 2022+专属方法。

方法二：SQL Server 2022+专属版本（保证顺序一致） #

SQL Server 2022新增了STRING_SPLIT的第三个参数enable_ordinal，可以直接返回拆分值在原字符串中的位置序号，彻底解决顺序问题：

WITH SplitText AS (
    SELECT 
        Name,
        value AS TextValue,
        ordinal AS Seq -- 直接获取拆分值的原始位置序号
    FROM YourTable
    CROSS APPLY STRING_SPLIT([Text], ';', 1) -- 第三个参数1启用序号返回
),
UnpivotedLoc AS (
    SELECT 
        Name,
        LocValue,
        Seq
    FROM (
        SELECT 
            Name,
            Loc1,
            Loc2,
            Loc3,
            Loc4
        FROM YourTable
    ) AS t
    UNPIVOT (
        LocValue FOR LocCols IN (Loc1, Loc2, Loc3, Loc4)
    ) AS u
    CROSS APPLY (
        SELECT CASE LocCols 
            WHEN 'Loc1' THEN 1 
            WHEN 'Loc2' THEN 2 
            WHEN 'Loc3' THEN 3 
            WHEN 'Loc4' THEN 4 
        END AS Seq
    ) AS seq_gen
    WHERE LocValue IS NOT NULL AND LocValue <> ''
)
SELECT 
    st.Name,
    st.TextValue,
    ul.LocValue
FROM SplitText st
INNER JOIN UnpivotedLoc ul 
    ON st.Name = ul.Name 
    AND st.Seq = ul.Seq;

额外调整建议 #

• 如果Text拆分后的数量和Loc列的非空数量不一致，可将INNER JOIN改为LEFT JOIN，保留所有拆分的Text值（即使对应Loc为空）。
• 如果存在Name重复的情况，确保你的表有唯一标识列（比如ID），并在PARTITION BY中加入该列，避免序号生成错误。

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