将表中多列逗号分隔值转换为单表结构的SQL方案咨询

AI 小北

2026-7-20

解决方案

假设你的源表名为 OriginalTable，可以通过逆透视（Unpivot）+ 字符串拆分两步实现需求，具体SQL代码如下：

方法1：使用`CROSS APPLY VALUES`逆透视 + 字符截取拆分

SELECT
    t.Userid,
    -- 截取逗号前的部分作为refid
    TRY_CAST(LEFT(col.CombinedValue, CHARINDEX(',', col.CombinedValue) - 1) AS INT) AS refid,
    -- 截取逗号后的部分作为value
    TRY_CAST(RIGHT(col.CombinedValue, LEN(col.CombinedValue) - CHARINDEX(',', col.CombinedValue)) AS INT) AS value
FROM OriginalTable t
-- 将Col2到Col5的多列转换为多行记录
CROSS APPLY (
    VALUES (Col2), (Col3), (Col4), (Col5)
) col(CombinedValue)
-- 过滤空值和格式不正确的记录（确保包含逗号）
WHERE col.CombinedValue IS NOT NULL
  AND CHARINDEX(',', col.CombinedValue) > 0

方法2：使用`STRING_SPLIT`拆分（适合格式统一的场景）

如果你的SQL Server版本支持STRING_SPLIT（2016及以上），也可以用这种方式：

SELECT
    t.Userid,
    MAX(CASE WHEN rn = 1 THEN s.value END) AS refid,
    MAX(CASE WHEN rn = 2 THEN s.value END) AS value
FROM OriginalTable t
-- 先逆透视多列
CROSS APPLY (
    VALUES (Col2), (Col3), (Col4), (Col5)
) col(CombinedValue)
-- 拆分每个逗号分隔的字符串
CROSS APPLY (
    SELECT 
        value,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rn
    FROM STRING_SPLIT(col.CombinedValue, ',')
) s
WHERE col.CombinedValue IS NOT NULL
GROUP BY t.Userid, col.CombinedValue

核心思路解析

逆透视转换：通过CROSS APPLY VALUES把原本分散在Col2-Col5的多列数据，转换成每行对应一个Userid和一个待拆分的字符串，这一步是把横向的列转成纵向的行，为后续统一拆分做准备。
字符串拆分：
- 方法1利用CHARINDEX定位逗号位置，再用LEFT和RIGHT截取前后两段，效率更高，适合每个字符串只有一个逗号的场景。
- 方法2用STRING_SPLIT拆分后，通过ROW_NUMBER标记拆分出的两个部分，再用CASE语句合并成refid和value字段，灵活性更强。