如何从竖线分隔字符串提取数据至多列并生成通用SQL脚本

问题背景 #

临时表#ParsedBlocks仅包含[BlockData]列，存储竖线（|）分隔的字符串，需将分隔后的内容提取到独立列中。当前使用的SUBSTRING脚本仅能处理前两列，无法覆盖多列场景；同时存在多个结构类似但分隔列数不同的表，需要通用脚本自动适配。

当前脚本及结果 #

现有提取脚本：

select * 
    ,SUBSTRING(BlockData, 1, CHARINDEX('|',BlockData) -1)
    ,SUBSTRING(BlockData, CHARINDEX('|', BlockData) + 1, LEN(BlockData))
from 
    #ParsedBlocks

执行结果：

源数据集（匹配期望结果修正版） #

期望结果 #

解决方案 #

1. 固定列数场景（以4列为例） #

若目标列数固定，可使用STRING_SPLIT（SQL Server 2022+支持序号）结合条件聚合实现：

WITH NumberedRows AS (
    -- 为每行生成唯一ID，避免重复BlockData导致分组错误
    SELECT BlockData, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowID
    FROM #ParsedBlocks
)
SELECT 
    MAX(CASE WHEN ordinal = 1 THEN value END) AS Column1,
    MAX(CASE WHEN ordinal = 2 THEN value END) AS Column2,
    MAX(CASE WHEN ordinal = 3 THEN value END) AS Column3,
    MAX(CASE WHEN ordinal = 4 THEN value END) AS Column4
FROM NumberedRows
CROSS APPLY STRING_SPLIT(BlockData, '|', 1) -- 第三个参数启用序号返回
GROUP BY RowID;

2. 通用动态SQL方案（适配任意列数） #

针对列数不固定的场景，可通过动态SQL自动识别最大列数并生成提取脚本：

兼容SQL Server 2022+版本

DECLARE @MaxColumns INT, @SQL NVARCHAR(MAX), @ColumnList NVARCHAR(MAX), @OrdinalList NVARCHAR(MAX);

-- 计算最大列数：分隔符数量+1
SELECT @MaxColumns = MAX(LEN(BlockData) - LEN(REPLACE(BlockData, '|', '')) + 1)
FROM #ParsedBlocks;

-- 生成列名列表（Column1, Column2...）
SET @ColumnList = STUFF((
    SELECT ', Column' + CAST(n AS VARCHAR(10))
    FROM (SELECT TOP(@MaxColumns) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_columns) t
    FOR XML PATH('')
), 1, 2, '');

-- 生成序号列表（1,2...）
SET @OrdinalList = STUFF((
    SELECT ', ' + CAST(n AS VARCHAR(10))
    FROM (SELECT TOP(@MaxColumns) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_columns) t
    FOR XML PATH('')
), 1, 2, '');

-- 构建动态SQL
SET @SQL = N'
WITH NumberedRows AS (
    SELECT BlockData, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowID
    FROM #ParsedBlocks
), SplitData AS (
    SELECT RowID, value, ordinal
    FROM NumberedRows
    CROSS APPLY STRING_SPLIT(BlockData, ''|'', 1)
)
SELECT ' + @ColumnList + N'
FROM SplitData
PIVOT (
    MAX(value)
    FOR ordinal IN (' + @OrdinalList + N')
) AS PivotResult;';

-- 执行动态SQL
EXEC sp_executesql @SQL;

兼容SQL Server 2016-2019版本

若使用的SQL Server版本不支持STRING_SPLIT的序号参数，先创建自定义拆分函数：

CREATE FUNCTION dbo.SplitStringWithOrdinal(
    @InputString NVARCHAR(MAX),
    @Delimiter NVARCHAR(10)
)
RETURNS TABLE 
AS
RETURN (
    SELECT 
        value,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ordinal
    FROM STRING_SPLIT(@InputString, @Delimiter)
);

然后修改动态SQL中的CROSS APPLY部分为：

CROSS APPLY dbo.SplitStringWithOrdinal(BlockData, '|')

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