질문 먼저 드립니다.

1) 하기 순서대로 나오면 되는건지?

2) 제 상식 기준에서 BOM 이라면 상기표의 형태가 아닌

CODE             CODE 1

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

ACODE          ACODE-1

ACODE-1       ACODE-1-2      

ACODE-1-3    ACODE-1-3-2

이 맞는듯 한데.. 일단 2가지 경우를 다 고려해서 쿼리를 짜볼께요..

(금강산도 식후경이니 일단 밥부터 먹구요 ㅡ,ㅡ...)

1) 상기 본문글에 있던 순번대로 나오는 쿼리

-------쿼리시작--------

with bom(CODE,CODE1,DVS) As
(
     Select 'ACODE'        ,'ACODE-1'       ,'P' Union All
     Select 'ACODE'        ,'ACODE-2'       ,'A' Union All
     Select 'ACODE'        ,'ACODE-3'       ,'A' Union All
     Select 'ACODE-1'      ,'ACODE-1-1'     ,'A' Union All
     Select 'ACODE-1'      ,'ACODE-1-2'     ,'P' Union All
     Select 'ACODE-1'      ,'ACODE-1-3'     ,'P' Union All
     Select 'ACODE-1-2'    ,'ACODE-1-2-1'   ,'P' Union All
     Select 'ACODE-1-2'    ,'ACODE-1-2-2'   ,'A' Union All
     Select 'ACODE-1-2'    ,'ACODE-1-2-3'   ,'P' Union All
     Select 'ACODE-1-3'    ,'ACODE-1-3-1'   ,'A' Union All
     Select 'ACODE-1-3'    ,'ACODE-1-3-2'   ,'P' Union All
     Select 'ACODE-1-3'    ,'ACODE-1-3-3'   ,'A'
)
,bom_ex(CODE,CODE1,DVS,SEQ) As
(
    Select a.*
          ,1
      From bom a
     Where Not Exists (   Select 'x' From bom b Where a.CODE = b.CODE1 )
       And a.DVS = 'P'
      
 Union All
 
    Select b.CODE
          ,b.CODE1
          ,b.DVS
          ,a.SEQ + 1
      From bom_ex a Inner Join bom b On a.CODE1 = b.CODE
     Where b.DVS = 'P'
)
,bom_ex_seq(CODE,CODE1,DVS,SEQ,SERIAL) As
(
    Select a.*
          ,ROW_NUMBER() Over(PARTITION By a.SEQ Order By a.CODE Asc,a.CODE1 Asc)
      From bom_ex a
)     
    Select a.*
      From bom_ex_seq a
  Order By a.SEQ asc
          ,a.SERIAL asc

----쿼리끝----

2) 순번대로..

단, 이건은 해당 BOM 테이블에

순번을 알수 있는 컬럼이 있어야 됩니다.

쿼리에서 컬럼 임의로 추가해서 사용했네요..

---쿼리시작---

with bom(CODE,CODE1,DVS,SEQ) As
(
     Select 'ACODE'        ,'ACODE-1'       ,'P',1 Union All
     Select 'ACODE'        ,'ACODE-2'       ,'A',2 Union All
     Select 'ACODE'        ,'ACODE-3'       ,'A',3 Union All
     Select 'ACODE-1'      ,'ACODE-1-1'     ,'A',1 Union All
     Select 'ACODE-1'      ,'ACODE-1-2'     ,'P',2 Union All
     Select 'ACODE-1'      ,'ACODE-1-3'     ,'P',3 Union All
     Select 'ACODE-1-2'    ,'ACODE-1-2-1'   ,'P',1 Union All
     Select 'ACODE-1-2'    ,'ACODE-1-2-2'   ,'A',2 Union All
     Select 'ACODE-1-2'    ,'ACODE-1-2-3'   ,'P',3 Union All
     Select 'ACODE-1-3'    ,'ACODE-1-3-1'   ,'A',1 Union All
     Select 'ACODE-1-3'    ,'ACODE-1-3-2'   ,'P',2 Union All
     Select 'ACODE-1-3'    ,'ACODE-1-3-3'   ,'A',3
)
,bom_ex(CODE,CODE1,DVS,SEQ,LEVL,SEQX) As
(
    Select a.*
          ,1
          ,' ' + right(' ' + convert(varchar(max),row_number() over(partition by a.CODE order by a.CODE,a.SEQ) ),3)
      From bom a
     Where Not Exists (   Select 'x' From bom b Where a.CODE = b.CODE1 )
       And a.DVS = 'P'
      
 Union All
 
    Select b.CODE
          ,b.CODE1
          ,b.DVS
          ,b.SEQ
          ,a.LEVL + 1
          ,a.SEQX + ' ' + convert(varchar(max),row_number() over(partition by a.CODE order by a.CODE,a.SEQ) )
      From bom_ex a Inner Join bom b On a.CODE1 = b.CODE
     Where b.DVS = 'P'
)
    Select a.*
      From bom_ex a
  Order By a.SEQX asc

---쿼리끝---         

복잡하긴 하지만..

야매로 Sequence 만들어서 사용..

-------쿼리시작--------

with bom(CODE,CODE1,DVS) As
(
     Select 'ACODE'        ,'ACODE-1'       ,'P' Union All
     Select 'ACODE'        ,'ACODE-2'       ,'A' Union All
     Select 'ACODE'        ,'ACODE-3'       ,'A' Union All
     Select 'ACODE-1'      ,'ACODE-1-1'     ,'A' Union All
     Select 'ACODE-1'      ,'ACODE-1-2'     ,'P' Union All
     Select 'ACODE-1'      ,'ACODE-1-3'     ,'P' Union All
     Select 'ACODE-1-2'    ,'ACODE-1-2-1'   ,'P' Union All
     Select 'ACODE-1-2'    ,'ACODE-1-2-2'   ,'A' Union All
     Select 'ACODE-1-2'    ,'ACODE-1-2-3'   ,'P' Union All
     Select 'ACODE-1-3'    ,'ACODE-1-3-1'   ,'A' Union All
     Select 'ACODE-1-3'    ,'ACODE-1-3-2'   ,'P' Union All
     Select 'ACODE-1-3'    ,'ACODE-1-3-3'   ,'A'
)
,bom_ex(CODE,CODE1,DVS,SEQ) As
(
    Select a.*
          ,1
      From bom a
     Where Not Exists (   Select 'x' From bom b Where a.CODE = b.CODE1 )
       And a.DVS = 'P'
      
 Union All
 
    Select b.CODE
          ,b.CODE1
          ,b.DVS
          ,a.SEQ + 1
      From bom_ex a Inner Join bom b On a.CODE1 = b.CODE
     Where b.DVS = 'P'
)
,bom_ex_seq(CODE,CODE1,DVS,SEQ,SERIAL) As
(
    Select a.*
          ,ROW_NUMBER() Over(PARTITION By a.SEQ Order By a.CODE Asc,a.CODE1 Asc)
      From bom_ex a
)     
,bom_ex_seq2(CODE,CODE1,DVS,SEQ,SERIAL,SERIAL2) As
(
    Select a.*
          ,ROW_NUMBER() Over ( Order By a.SERIAL ASC)
      From bom_ex_seq a
)     
    Select a.*
          ,REPLICATE('z',a.SEQ) + ' ' + CONVERT(varchar(3),a.SERIAL)
      From bom_ex_seq2 a
  Order By a.SERIAL2 asc

----쿼리끝----

재귀쿼리라는 거군요

이해하는데 좀 오래 걸렸습니다.

수고 하셨습니다.

고맙습니다.