求替代料处理语句?

[bigfoot001]

表结构: BOM表
BOM_NO nvarchar(40), --BOM号
PRD_NO nvarchar(40), --料号
PRD_NO_REP nvarchar(250) --替代料

BOM数据:
PC00001, 100-2300013, 100-2300013A;100-2300013B
PC00001, 100-2300014, 100-2300014B;100-2300014D
PC00002, 100-2300013, 100-2300013B;100-2300013D
PC00002, 100-2300014A, 100-2300014;100-2300014B;100-2300014D

--说明: 100-2300013A;100-2300013B为100-2300013的替代料; 替代料是用分号";"分隔的。

现在要新加一个替代料表: PRDT_REP 结构:
PRD_NO nvarchar(40), --料号
ITEM integer, --序号
PRD_NO_REP nvarchar(40) --替代料

并将BOM中数据写入到替代料表中;
100-2300013, 1, 100-2300013A
100-2300013, 2, 100-2300013B
100-2300013, 3, 100-2300013D
100-2300014, 1, 100-2300014B
100-2300014, 2, 100-2300014D
100-2300014A, 1, 100-2300014
100-2300014A, 2, 100-2300014B
100-2300014A, 3, 100-2300014D

最后再根据替代料表更新BOM表成为:
PC00001, 100-2300013, 1;2
PC00001, 100-2300014, 1;2
PC00002, 100-2300013, 2;3
PC00002, 100-2300014A, 1;2;3

--简单说: 原来BOM中存的是替代料号，现在要求存是的替代料表中的ITEM号; 不知表达得明白？

[viva369]

--创建表
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

[bigfoot001]

谢谢1楼回复: 有谁能给出SQL 2000写法, 及考虑替代料非常多的情况.

[唐诗三百首]

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
100-2300013            2        100-2300013B
100-2300013            3        100-2300013D
100-2300014            1        100-2300014B
100-2300014            2        100-2300014D
100-2300014A           1        100-2300014
100-2300014A           2        100-2300014B
100-2300014A           3        100-2300014D

update t1 set t1.PRD_NO_REP=left(t2.PRD_NO_REP2,len(t2.PRD_NO_REP2)-1)
from BOM t1
inner join 
(select a.PRD_NO,a.PRD_NO_REP,
cast(
(select case 
 when charindex(b.PRD_NO_REP,a.PRD_NO_REP,1)>0 then cast(b.ITEM as varchar)+';' else '' end
 from PRDT_REP b where b.PRD_NO=a.PRD_NO for xml path('')) as varchar) 'PRD_NO_REP2'
from BOM a) t2
on t1.PRD_NO=t2.PRD_NO and t1.PRD_NO_REP=t2.PRD_NO_REP

select * from BOM

BOM_NO              PRD_NO           PRD_NO_REP
------------ -------------------- ---------------
PC00001           100-2300013         1;2
PC00001           100-2300014         1;2
PC00002           100-2300013         2;3
PC00002           100-2300014A        1;2;3