TSQL按条件将行值拆分至多列的技术求助

看起来你已经尝试了ROW_NUMBER()但没找对正确的分组方式，其实核心是先给每个值标记类型，再按类型分区生成组号，最后用条件聚合把同组的不同类型值转到同一行的对应列里。

先看你的样本数据：

Col1值：0183125031218、13020690831、79100146868、0183225031138、13010690834、79100145497

期望输出是每行一组三个值：

解决方案：条件聚合 + 分区行号 #

下面是可以直接运行的SQL代码，我会一步步解释：

WITH categorized_data AS (
    SELECT
        Col1,
        -- 标记每个值的类型：01前缀、13前缀、其他
        CASE
            WHEN LEFT(Col1, 2) = '01' THEN 'Type01'
            WHEN LEFT(Col1, 2) = '13' THEN 'Type13'
            ELSE 'TypeOther'
        END AS ValueType,
        -- 按类型分区生成行号，确保同类型的第n个值对应同一组
        ROW_NUMBER() OVER (PARTITION BY 
            CASE
                WHEN LEFT(Col1, 2) = '01' THEN 'Type01'
                WHEN LEFT(Col1, 2) = '13' THEN 'Type13'
                ELSE 'TypeOther'
            END 
            ORDER BY Col1) AS GroupNum
    FROM YourTableName -- 替换成你的实际表名
)
SELECT
    MAX(CASE WHEN ValueType = 'Type01' THEN Col1 END) AS Col1,
    MAX(CASE WHEN ValueType = 'Type13' THEN Col1 END) AS Col2,
    MAX(CASE WHEN ValueType = 'TypeOther' THEN Col1 END) AS Col3
FROM categorized_data
GROUP BY GroupNum
ORDER BY GroupNum;

关键步骤解释： #

• CTE分类数据：
  • 用LEFT(Col1,2)判断前缀，给每个值打上类型标签。
  • ROW_NUMBER() OVER (PARTITION BY ValueType ORDER BY Col1)：按类型分区生成行号，这样01前缀的第1个值、13前缀的第1个值、其他的第1个值，GroupNum都是1，对应第一组；以此类推。
• 条件聚合转列：
  • 按GroupNum分组，用MAX(CASE...)把同组内不同类型的值提取到对应列。这里用MAX是因为每组内每个类型只有一个值，聚合函数只是用来筛选出对应值。

为什么你之前用ROW_NUMBER没成功？ #

大概率是没有按类型分区，而是直接全局生成行号，导致同组的三个值行号不一致，无法正确分组转列。上面的代码核心就是通过分区行号把不同类型的同组值关联起来。

如果你的数据顺序有特定要求（比如不是按Col1排序），可以把ORDER BY Col1改成你需要的排序字段，比如插入时间之类的。

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