--创建表
create table BOM
(
    BOM_NO nvarchar(40), --BOM号
    PRD_NO nvarchar(40), --料号
    PRD_NO_REP nvarchar(250) --替代料
)
go
create table PRDT_REP
(
PRD_NO nvarchar(40), --料号
ITEM integer, --序号
PRD_NO_REP nvarchar(40) --替代料
)

go
--新增测试数据
insert BOM
select 'PC00001', '100-2300013', '100-2300013A;100-2300013B'  union all
select 'PC00001', '100-2300014', '100-2300014B;100-2300014D'   union all
select 'PC00002', '100-2300013', '100-2300013B;100-2300013D'  union all
select 'PC00002', '100-2300014A', '100-2300014;100-2300014B;100-2300014D'

--并将BOM中数据写入到替代料表中
declare @sql varchar(max)
set @sql = 'select '

select @sql=@sql+''''+prd_no+''' a,'''+replace(PRD_NO_REP,';',''' b union all select '''+prd_no+''',''')+''' union all select ' from BOM

set @sql = 'insert PRDT_REP select a,row_number()over(partition by a order by b) id,b from ('+left(@sql,len(@sql) - 17)+')tbl group by a,b'
exec (@sql)

go

--创建字符串聚合函数
create function FN_GetStr
(
    @PRD_NO nvarchar(40), --料号
    @PRD_NO_REP nvarchar(500) --替代料
)
returns nvarchar(250)
as
begin 
    declare @result nvarchar(500)
    set @result = ''
    select @result = @result + ltrim(item) + ','
    from PRDT_REP
    where PRD_NO = @PRD_NO and charindex(';'+prd_no_rep+';',';'+@PRD_NO_REP+';') > 0

    return left(@result,len(@result)-1)
end
go

--更新
update BOM set prd_no_rep = tbl.result
from
(
    select *,dbo.FN_GetStr(PRD_NO,PRD_NO_REP) result from BOM
)tbl
where 
BOM.bom_no = tbl.bom_no and
BOM.PRD_NO = tbl.PRD_NO and
BOM.PRD_NO_REP = tbl.PRD_NO_REP

--返回最终数据
select * from BOM

------解决方案--------------------

SQL code

create table BOM
(
    BOM_NO nvarchar(40),
    PRD_NO nvarchar(40),
    PRD_NO_REP nvarchar(250)
)

create table PRDT_REP
(
PRD_NO nvarchar(40),
ITEM integer,
PRD_NO_REP nvarchar(40)
)

insert BOM
select 'PC00001', '100-2300013', '100-2300013A;100-2300013B' union all
select 'PC00001', '100-2300014', '100-2300014B;100-2300014D' union all
select 'PC00002', '100-2300013', '100-2300013B;100-2300013D' union all
select 'PC00002', '100-2300014A', '100-2300014;100-2300014B;100-2300014D'

insert into PRDT_REP 
select t.PRD_NO,
row_number() over (partition by PRD_NO order by PRD_NO_REP) 'ITEM',
t.PRD_NO_REP
from 
(select distinct a.PRD_NO,
substring(a.PRD_NO_REP,b.number,charindex(';',a.PRD_NO_REP+';',b.number)-b.number) 'PRD_NO_REP'
from BOM a, master.dbo.spt_values b
where b.type='P' and b.number between 1 and len(a.PRD_NO_REP)
and substring(';'+a.PRD_NO_REP,b.number,1)=';') t
group by PRD_NO,PRD_NO_REP

select * from PRDT_REP

PRD_NO                ITEM      PRD_NO_REP
-----------------  ---------- --------------
100-2300013            1        100-2300013A
1