如何将同表中逗号分隔列的值拆分并更新至对应列

问题背景 #

你手头有如下表结构和测试数据，需要把每行Codes列里的逗号分隔值拆分后，依次填充到C1至C5列（实际场景可能要扩展到N列），同时想避开动态SQL，寻找更简洁高效的实现方案。

原始表定义与测试数据 #

CREATE TABLE _Table ( 
    [Pat] NVARCHAR(8), 
    [Codes] NVARCHAR(50), 
    [C1] NVARCHAR(6), 
    [C2] NVARCHAR(6), 
    [C3] NVARCHAR(6), 
    [C4] NVARCHAR(6), 
    [C5] NVARCHAR(6) 
); 
GO 

INSERT INTO _Table ([Pat], [Codes], [C1], [C2], [C3], [C4], [C5]) 
VALUES 
    ('Pat1', 'U212,Y973,Y982', null, null, null, null, null), 
    ('Pat2', 'M653', null, null, null, null, null), 
    ('Pat3', 'U212,Y973,Y983,Z924,Z926', null, null, null, null, null); 
GO 

预期结果 #

最终要得到如下格式的输出：

Pat   Codes                          C1    C2    C3    C4    C5
Pat1  'U212,Y973,Y982'              U212  Y973  Y982  NULL  NULL
Pat2  'M653'                        M653  NULL  NULL  NULL  NULL
Pat3  'U212,Y973,Y983,Z924,Z926'    U212  Y973  Y983  Z924  Z926

解决方案 #

方案1：固定列数（C1-C5）的非动态SQL实现 #

如果列数是固定的，我们可以用字符串拆分函数+条件聚合的组合来实现，完全不需要动态SQL。这里以SQL Server为例，使用STRING_SPLIT函数（需SQL Server 2016及以上版本），结合ROW_NUMBER()标记拆分后值的位置，再通过MAX(CASE...)完成行转列：

WITH SplitCodes AS (
    SELECT 
        Pat,
        Codes,
        value AS Code,
        -- 按行分组标记每个拆分值的序号
        ROW_NUMBER() OVER (PARTITION BY Pat ORDER BY (SELECT NULL)) AS CodeIndex
    FROM _Table
    CROSS APPLY STRING_SPLIT(Codes, ',')
)
SELECT 
    Pat,
    Codes,
    MAX(CASE WHEN CodeIndex = 1 THEN Code END) AS C1,
    MAX(CASE WHEN CodeIndex = 2 THEN Code END) AS C2,
    MAX(CASE WHEN CodeIndex = 3 THEN Code END) AS C3,
    MAX(CASE WHEN CodeIndex = 4 THEN Code END) AS C4,
    MAX(CASE WHEN CodeIndex = 5 THEN Code END) AS C5
FROM SplitCodes
GROUP BY Pat, Codes;

注意：低版本SQL Server中STRING_SPLIT无法保证拆分顺序和原字符串完全一致，如果需要严格保持顺序，SQL Server 2022+可以用STRING_SPLIT(Codes, ',', 1)启用序号参数；低版本则需要用自定义的XML拆分函数来确保顺序。

方案2：支持扩展到N列的通用思路 #

如果实际场景需要适配不确定数量的列，又不想用动态SQL，可以参考以下两种思路：

思路A：返回结构化JSON/XML（无需固定列）

如果业务允许不生成物理列C1/C2...，可以直接返回拆分后的结构化数据，方便后续程序处理：

SELECT 
    Pat,
    Codes,
    (
        SELECT Code AS [value]
        FROM STRING_SPLIT(Codes, ',')
        FOR JSON PATH
    ) AS CodesArray
FROM _Table;

思路B：自定义拆分函数+条件聚合（兼容低版本）

如果是SQL Server 2016以下版本，没有STRING_SPLIT，可以先创建一个带序号的自定义拆分函数，再用条件聚合转列：

CREATE FUNCTION dbo.SplitString
(
    @String NVARCHAR(MAX),
    @Delimiter CHAR(1)
)
RETURNS @Results TABLE (
    Item NVARCHAR(MAX),
    ItemIndex INT IDENTITY(1,1)
)
AS
BEGIN
    DECLARE @StartIndex INT, @EndIndex INT

    SET @StartIndex = 1
    IF SUBSTRING(@String, LEN(@String) - 1, LEN(@String)) <> @Delimiter
    BEGIN
        SET @String = @String + @Delimiter
    END

    WHILE CHARINDEX(@Delimiter, @String) > 0
    BEGIN
        SET @EndIndex = CHARINDEX(@Delimiter, @String)
        
        INSERT INTO @Results(Item)
        SELECT SUBSTRING(@String, @StartIndex, @EndIndex - @StartIndex)
        
        SET @String = SUBSTRING(@String, @EndIndex + 1, LEN(@String))
    END

    RETURN
END
GO

-- 使用自定义函数完成拆分转列
WITH SplitCodes AS (
    SELECT 
        Pat,
        Codes,
        Item AS Code,
        ItemIndex AS CodeIndex
    FROM _Table
    CROSS APPLY dbo.SplitString(Codes, ',')
)
SELECT 
    Pat,
    Codes,
    MAX(CASE WHEN CodeIndex = 1 THEN Code END) AS C1,
    MAX(CASE WHEN CodeIndex = 2 THEN Code END) AS C2,
    MAX(CASE WHEN CodeIndex = 3 THEN Code END) AS C3,
    MAX(CASE WHEN CodeIndex = 4 THEN Code END) AS C4,
    MAX(CASE WHEN CodeIndex = 5 THEN Code END) AS C5
    -- 如需扩展N列，继续添加对应CASE语句即可
FROM SplitCodes
GROUP BY Pat, Codes;

补充：动态SQL适配任意N列 #

如果必须生成对应数量的物理列C1-CN，动态SQL是不可避免的，但可以优化写法，自动适配最大拆分数量：

DECLARE @MaxColumns INT, @SQL NVARCHAR(MAX), @CaseStatements NVARCHAR(MAX)

-- 获取Codes列中最多的拆分值数量
SELECT @MaxColumns = MAX((LEN(Codes) - LEN(REPLACE(Codes, ',', '')) + 1))
FROM _Table

-- 动态生成CASE语句
SET @CaseStatements = ''
WHILE @MaxColumns > 0
BEGIN
    SET @CaseStatements = @CaseStatements + 
        'MAX(CASE WHEN CodeIndex = ' + CAST(@MaxColumns AS NVARCHAR) + ' THEN Code END) AS C' + CAST(@MaxColumns AS NVARCHAR) + ','
    SET @MaxColumns = @MaxColumns - 1
END
-- 移除最后一个多余的逗号
SET @CaseStatements = LEFT(@CaseStatements, LEN(@CaseStatements) - 1)

-- 拼接并执行最终SQL
SET @SQL = '
WITH SplitCodes AS (
    SELECT 
        Pat,
        Codes,
        value AS Code,
        ROW_NUMBER() OVER (PARTITION BY Pat ORDER BY (SELECT NULL)) AS CodeIndex
    FROM _Table
    CROSS APPLY STRING_SPLIT(Codes, '','')
)
SELECT 
    Pat,
    Codes,
    ' + @CaseStatements + '
FROM SplitCodes
GROUP BY Pat, Codes;'

EXEC sp_executesql @SQL

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