BOM T-SQL

assy_nbr為父件
item_nbr為子件

CREATE PROC bom_tst
AS
DECLARE @t TABLE(assy_nbr varchar(20),Level int,Path varchar(8000))
DECLARE @l int
SET @l=0
INSERT @t SELECT DISTINCT assy_nbr,@l,RIGHT(SPACE(20)+assy_nbr,20)
FROM inv_bom a
WHERE NOT EXISTS(
SELECT * FROM inv_bom WHERE item_nbr=a.assy_nbr)
WHILE @@ROWCOUNT>0
BEGIN
SET @l=@l+1
INSERT @t SELECT a.item_nbr,@l,b.Path+RIGHT(SPACE(20)+a.item_nbr,20)
FROM inv_bom a,@t b
WHERE a.assy_nbr=b.assy_nbr AND b.Level=@l-1
END
SELECT assy_nbr=CASE Level WHEN 0 THEN '' ELSE '|'+REPLICATE('-',Level) END+assy_nbr,
Level
FROM @t
ORDER BY Path
GO