如何在Microsoft SQL Server中无循环拆分数据为列与值？

一、将非结构化字符串转换为结构化数据 #

通过CTE拆分键值对并分组，结合PIVOT实现行转列，全程无需WHILE循环：

DECLARE @rawData NVARCHAR(MAX) = 'id:123;qty:1,00;price:1,25;sprice:2,00;id:125;qty:2,00;price:2,25;sprice:3,00;'

WITH KeyValuePairs AS (
    -- 拆分分号，过滤末尾多余分号产生的空行
    SELECT 
        TRIM(value) AS KVPair
    FROM STRING_SPLIT(@rawData, ';')
    WHERE TRIM(value) <> ''
),
SplitKV AS (
    -- 拆分键值，同时按每4个键值对分组（对应一行结构化记录）
    SELECT
        (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) / 4 AS RowGroup,
        LEFT(KVPair, CHARINDEX(':', KVPair) - 1) AS ColumnName,
        RIGHT(KVPair, LEN(KVPair) - CHARINDEX(':', KVPair)) AS ColumnValue
    FROM KeyValuePairs
)
-- 行转列生成结构化表
SELECT 
    [id], [qty], [price], [sprice]
FROM SplitKV
PIVOT (
    MAX(ColumnValue)
    FOR ColumnName IN ([id], [qty], [price], [sprice])
) AS PivotedData;

兼容旧版SQL Server（无STRING_SPLIT） #

若使用SQL Server 2016之前的版本，用系统表替代拆分函数，同样无需WHILE循环：

DECLARE @rawData NVARCHAR(MAX) = 'id:123;qty:1,00;price:1,25;sprice:2,00;id:125;qty:2,00;price:2,25;sprice:3,00;'

WITH KeyValuePairs AS (
    SELECT 
        TRIM(SUBSTRING(@rawData, number, CHARINDEX(';', @rawData + ';', number) - number)) AS KVPair
    FROM master.dbo.spt_values
    WHERE type = 'P' AND number <= LEN(@rawData)
        AND SUBSTRING(';' + @rawData, number, 1) = ';'
),
SplitKV AS (
    SELECT
        (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) / 4 AS RowGroup,
        LEFT(KVPair, CHARINDEX(':', KVPair) - 1) AS ColumnName,
        RIGHT(KVPair, LEN(KVPair) - CHARINDEX(':', KVPair)) AS ColumnValue
    FROM KeyValuePairs
)
SELECT 
    [id], [qty], [price], [sprice]
FROM SplitKV
PIVOT (
    MAX(ColumnValue)
    FOR ColumnName IN ([id], [qty], [price], [sprice])
) AS PivotedData;

二、提取列名并构造动态INSERT语句 #

通过去重列名并聚合拼接，生成目标动态SQL：

DECLARE @rawData NVARCHAR(MAX) = 'id:123;qty:1,00;price:1,25;sprice:2,00;id:125;qty:2,00;price:2,25;sprice:3,00;'
DECLARE @columns NVARCHAR(MAX);
DECLARE @query NVARCHAR(MAX);

-- 提取去重列名并拼接成逗号分隔格式
WITH KeyValuePairs AS (
    SELECT 
        TRIM(value) AS KVPair
    FROM STRING_SPLIT(@rawData, ';')
    WHERE TRIM(value) <> ''
),
SplitKV AS (
    SELECT DISTINCT
        LEFT(KVPair, CHARINDEX(':', KVPair) - 1) AS ColumnName
    FROM KeyValuePairs
)
SELECT @columns = STRING_AGG(QUOTENAME(ColumnName), ', ')
FROM SplitKV;

-- 构造并执行动态INSERT语句
SET @query = 'INSERT INTO xtable (' + @columns + ')
SELECT ' + @columns + ' FROM ytable';

EXEC sp_executesql @query;

兼容旧版SQL Server（无STRING_AGG） #

用FOR XML PATH替代聚合函数完成列名拼接：

DECLARE @rawData NVARCHAR(MAX) = 'id:123;qty:1,00;price:1,25;sprice:2,00;id:125;qty:2,00;price:2,25;sprice:3,00;'
DECLARE @columns NVARCHAR(MAX);
DECLARE @query NVARCHAR(MAX);

WITH KeyValuePairs AS (
    SELECT 
        TRIM(value) AS KVPair
    FROM STRING_SPLIT(@rawData, ';')
    WHERE TRIM(value) <> ''
),
SplitKV AS (
    SELECT DISTINCT
        QUOTENAME(LEFT(KVPair, CHARINDEX(':', KVPair) - 1)) AS ColumnName
    FROM KeyValuePairs
)
SELECT @columns = STUFF((
    SELECT ', ' + ColumnName
    FROM SplitKV
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

SET @query = 'INSERT INTO xtable (' + @columns + ')
SELECT ' + @columns + ' FROM ytable';

EXEC sp_executesql @query;

核心思路说明 #

• 用内置字符串拆分函数（或兼容方案）替代WHILE循环拆分字符串
• 通过ROW_NUMBER()分组，将连续键值对映射为结构化表的行
• 用PIVOT实现行转列，生成目标结构化数据
• 用字符串聚合函数（或XML拼接方案）动态生成列名字符串，构造INSERT语句

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