BOM과 같은 계층구조 -WITH사용 예

CREATE TABLE BomTest

( 

  TeamCode varchar(4)

 ,TeamName varchar(20)

 ,ParentCode varchar(4)

)

GO

Insert into BomTest(TeamCode, TeamName,ParentCode)

select '0001', '회사', '0000' union all

select '1001', '생산부', '0001' union all

select '1010', '생산1팀', '1001' union all

select '1011', '선행조립', '1010' union all

select '1012', '후행조립','1010' union all

select '1020', '생산2팀', '1001' union all

select '1021', '금형가공', '1020' union all

select '1022', '금형조립', '1020' union all

select '1030', '의장1팀', '1001' union all

select '1040', '의장2팀', '1001' union all

select '2001', '영업부', '0001' union all

select '2010', '영업1팀', '2001' union all

select '2020', '영업2팀', '2001' union all

select '3001', '자재부', '0001' union all

select '3010', '구매1팀', '3001' union all

select '3020', '구매2팀', '3001'

 

-----------------------------------------

Select * from BomTest

-----------------------------------------

with ctest (TeamCode, TeamName,ParentCode, level)

as 

(

   select TeamCode, TeamName,ParentCode , 0 as level  from BomTest  where ParentCode='0000'

union all

   select t.TeamCode, t.TeamName,t.ParentCode, (c.level+1)

   from BomTest t join ctest c on t.ParentCode = c.TeamCode

)

select * from ctest