如何用SQL将CSV格式物品序列号列拆分为指定列及剩余列？

可以通过字符串拆分+行号标记+聚合拼接的方式实现需求，以下是针对SQL Server的完整实现代码：

WITH SplitItems AS (
    SELECT 
        A_NUMBER,
        value AS Item,
        -- 为每个拆分后的物品标记序号，保证顺序
        ROW_NUMBER() OVER (PARTITION BY A_NUMBER ORDER BY (SELECT NULL)) AS RN
    FROM itemTable
    -- 拆分逗号分隔的items列
    CROSS APPLY STRING_SPLIT(items, ',')
)
SELECT
    -- 提取前4个物品序列号，无则显示空字符串
    MAX(CASE WHEN RN = 1 THEN Item ELSE '' END) AS Srllno1,
    MAX(CASE WHEN RN = 2 THEN Item ELSE '' END) AS Srllno2,
    MAX(CASE WHEN RN = 3 THEN Item ELSE '' END) AS Srllno3,
    MAX(CASE WHEN RN = 4 THEN Item ELSE '' END) AS Srllno4,
    -- 拼接第4个之后的所有物品，去掉开头多余的逗号
    STUFF(
        (SELECT ',' + Item 
         FROM SplitItems si 
         WHERE si.A_NUMBER = s.A_NUMBER AND si.RN > 4 
         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
        1, 1, ''
    ) AS SrllRest
FROM SplitItems s
GROUP BY A_NUMBER;

代码说明： #

1. SplitItems CTE：通过STRING_SPLIT将每个items字段拆分为独立行，并用ROW_NUMBER()为每行标记序号，确保物品顺序和原字符串一致。
2. 提取前4列：使用CASE WHEN结合MAX聚合，按序号提取前4个物品，没有对应序号的显示空字符串。
3. 生成SrllRest：通过FOR XML PATH将序号大于4的物品拼接成逗号分隔的字符串，STUFF函数用于移除开头多余的逗号；如果没有多余物品，该字段自动为空。

注意事项： #

• 如果你的SQL Server兼容级别低于130，STRING_SPLIT无法使用，可替换为自定义的字符串拆分函数（如基于递归CTE的拆分方法）。
• ORDER BY (SELECT NULL)是为了兼容不同环境，若需严格保证拆分顺序，建议将数据库兼容级别设为130及以上，此时STRING_SPLIT支持ordinal列，可改为ORDER BY ordinal。

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