请求协助SQL PIVOT函数实现分隔值转多列（无STRING_SPLIT支持）

我有一张用“-”分隔值的参考表，需要把分隔后的值拆分到多列，但所用SQL数据库不支持STRING_SPLIT函数。目前已经通过多CTE把结果拆分成了多行，现在需要完成PIVOT部分（或等效语句），得到预期格式的结果。

原始数据 #

现有代码 #

declare @T table (ID int, Col varchar(100), description varchar(50))
insert into @T values (1, 'MV-RUC-DEBT-ASSESS', 'MV Debt Assessment')
;

with cte as (
  select    a.ID 
            ,replace(a.Col,'-', ' ') as "Col"
            , a.description
        from @T a
),

cte2 as (
  select    a.ID
            , n.r.value('.', 'varchar(50)') "Value"
            , a.description
              from cte a
  cross apply (select cast('<r>'+replace(replace(Col,'&','&amp;'), ' ', '</r><r>')+'</r>' as xml)) as S(XMLCol)
  cross apply S.XMLCol.nodes('r') as n(r)
  
)

select *
from cte2 a
pivot 
(max(value) for a.ID in ([1], [2], [3], [4])) as "Pivot"

预期结果 #

修正方案 #

现有代码的核心问题是PIVOT的依据错误——你用原表的ID作为转列字段，但实际上需要按拆分后每个值的顺序编号来转列。以下是修正后的代码：

declare @T table (ID int, Col varchar(100), description varchar(50))
insert into @T values (1, 'MV-RUC-DEBT-ASSESS', 'MV Debt Assessment')
;

with cte as (
  select    a.ID 
            ,replace(a.Col,'-', ' ') as "Col"
            , a.description
        from @T a
),

cte2 as (
  select    a.ID
            , n.r.value('.', 'varchar(50)') "Value"
            , a.description
            -- 生成每个拆分值的顺序编号
            , row_number() over(partition by a.ID order by (select null)) as ItemSeq
              from cte a
  cross apply (select cast('<r>'+replace(replace(Col,'&','&amp;'), ' ', '</r><r>')+'</r>' as xml)) as S(XMLCol)
  cross apply S.XMLCol.nodes('r') as n(r)
  
)

-- 按顺序编号转列，保留ID和描述字段
select ID, Description, [1], [2], [3], [4]
from cte2
pivot 
(max(Value) for ItemSeq in ([1], [2], [3], [4])) as PivotResult

关键修改说明 #

1. 在cte2中新增ItemSeq列：用row_number()按原表ID分组，为每个拆分后的片段生成顺序编号，确保每个值对应正确的列位置。
2. 调整PIVOT逻辑：将转列依据从原表ID改为ItemSeq，这样就能把同一ID下的多个拆分值横向排列到对应序号的列中。
3. 明确指定输出列：最终SELECT语句保留ID、Description以及转后的[1]-[4]列，完全匹配预期结果格式。

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