SQL拆分复合结构varchar类型values列的实现方案咨询

一、纯SQL实现（适配SQL Server 2016及以上版本） #

你用到了CHARINDEX等函数且提及SSIS，默认是SQL Server环境，借助2016版本推出的OPENJSON函数即可稳定实现拆分，不需要复杂的字符串截取逻辑：

1. 输出形式1：按id关联拆分为多行，每行存单个数值 #

SELECT
    t.id,
    single_value = TRIM(num.value)
FROM 你的表名 t
-- 先把整个字符串转成所有数值组成的JSON数组
CROSS APPLY OPENJSON('["' + REPLACE(REPLACE(t.[values], '|', '","'), ';', '","') + '"]') num
-- 如果要过滤空值可加：WHERE num.value != ''

2. 输出形式2：按组拆分为x1、y1、x2、y2等多列 #

如果每组数量固定，直接按序号取值即可，示例为最多支持3组的写法：

SELECT
    t.id,
    x1 = JSON_VALUE(group_arr, '$[0][0]'),
    y1 = JSON_VALUE(group_arr, '$[0][1]'),
    x2 = JSON_VALUE(group_arr, '$[1][0]'),
    y2 = JSON_VALUE(group_arr, '$[1][1]'),
    x3 = JSON_VALUE(group_arr, '$[2][0]'),
    y3 = JSON_VALUE(group_arr, '$[2][1]')
FROM (
    SELECT 
        id,
        -- 转成二维JSON数组，结构为[[x1,y1],[x2,y2]]
        group_arr = '[["' + REPLACE(REPLACE(t.[values], '|', '"],["'), ';', '","') + '"]]'
    FROM 你的表名 t
) t

如果每组数量不固定，可拼接动态SQL生成对应列，代码如下：

DECLARE @max_group INT, @sql NVARCHAR(MAX)
-- 先计算最多有多少组
SELECT @max_group = MAX(LEN([values]) - LEN(REPLACE([values], '|', '')) + 1) FROM 你的表名

-- 拼接查询列
;WITH num_cte AS (
    SELECT 1 AS n UNION ALL SELECT n+1 FROM num_cte WHERE n < @max_group
)
SELECT @sql = STRING_AGG('
    x' + CAST(n AS VARCHAR) + ' = JSON_VALUE(group_arr, ''$[' + CAST(n-1 AS VARCHAR) + '][0]''),
    y' + CAST(n AS VARCHAR) + ' = JSON_VALUE(group_arr, ''$[' + CAST(n-1 AS VARCHAR) + '][1]'')', ',')
FROM num_cte

-- 执行动态SQL
SET @sql = '
SELECT id, ' + @sql + '
FROM (
    SELECT id, group_arr = ''[["'' + REPLACE(REPLACE([values], ''|'', ''"],["''), '';'', ''","'') + ''"]]''
    FROM 你的表名
) t'
EXEC sp_executesql @sql

二、Python脚本实现（容错性更高，适合复杂场景） #

如果数据存在格式异常，或者不想写复杂SQL，用Python处理更灵活，示例代码如下：

import pandas as pd
import pyodbc

# 连接数据库，替换为你的连接信息
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=你的服务器地址;DATABASE=你的数据库名;UID=账号;PWD=密码')
df = pd.read_sql("SELECT id, [values] FROM 你的表名", conn)

# 1. 生成单行单数值的表
row_list = []
for _, row in df.iterrows():
    id_val = row['id']
    for group in row['values'].split('|'):
        try:
            x, y = group.split(';')
            row_list.append({'id': id_val, 'value': float(x.strip())})
            row_list.append({'id': id_val, 'value': float(y.strip())})
        except:
            # 格式异常的行可自行处理，这里示例跳过
            continue
df_single = pd.DataFrame(row_list)
# 写回数据库，替换为你要写入的表名
df_single.to_sql('拆分后单行表', conn, if_exists='replace', index=False)

# 2. 生成x1 y1多列的表
col_list = []
for _, row in df.iterrows():
    id_val = row['id']
    tmp = {'id': id_val}
    for idx, group in enumerate(row['values'].split('|'), 1):
        try:
            x, y = group.split(';')
            tmp[f'x{idx}'] = float(x.strip())
            tmp[f'y{idx}'] = float(y.strip())
        except:
            continue
    col_list.append(tmp)
df_cols = pd.DataFrame(col_list)
# 写回数据库
df_cols.to_sql('拆分后多列表', conn, if_exists='replace', index=False)

conn.close()

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