with
_Reference188 as
(
select 1 _IDRRef, 0 _Fld675, 'TM._Description_1' _Description union all
select 2 _IDRRef, 0 _Fld675, 'TM._Description_2' _Description
)
,
_Reference105X1 as(
select 1 [_Fld9604RRef], '2' _Fld9602, 0 _Fld675, 0 _Marked, null [_IDRRef], 1 [_ParentIDRRef], '_Description' _Description, 1 _Fld9626RRef union all
select 1 [_Fld9604RRef], '2' _Fld9602, 0 _Fld675, 0 _Marked, 1    [_IDRRef], 2 [_ParentIDRRef], '_Description' _Description, 2 _Fld9626RRef 
),
_Enum403 as (
select 1 [_IDRRef], 0 [_EnumOrder]
),
V1 as (
             select a.*,
                    0 level,
                    a.[_IDRRef] source_id
             from _Reference105X1 a
             join _Enum403 e  on a.[_Fld9604RRef] = e.[_IDRRef]
             where a._Marked = 0
             and _Fld9602 > '0'
             and a._Fld675 = 0      
             and e.[_EnumOrder] in (0, 2, 4, 6)
            
             union all
            
             select a.*,
                    level + 1 level,
                    source_id
               from _Reference105X1 a
               join V1 b
                 on a.[_IDRRef] = b.[_ParentIDRRef]
                 and a._Fld675 = 0
--           and a._Marked  = 0                 
       ),
      
V2 AS
(
   select *,
             max(level) over (partition by source_id) max_level
       from V1
),

V3 AS
(
SELECT      
        SOURCE_ID,
--   max_level,
--   level,

       max(case when max_level between 1 and 9  and level = max_level   then _IDRRef         end) "LVL1_ID",
       max(case when max_level between 1 and 9  and level = max_level   then _Description    end) "LVL1_DESCR",
       max(case when max_level between 2 and 9  and level = max_level-1 then _IDRRef         end) "LVL2_ID",
       max(case when max_level between 2 and 9  and level = max_level-1 then _Description    end) "LVL2_DESCR",
       max(case when max_level between 3 and 9  and level = max_level-2 then _IDRRef         end) "LVL3_ID",
       max(case when max_level between 3 and 9  and level = max_level-2 then _Description    end) "LVL3_DESCR",
       max(case when max_level between 4 and 9  and level = max_level-3 then _IDRRef         end) "LVL4_ID",
       max(case when max_level between 4 and 9  and level = max_level-3 then _Description    end) "LVL4_DESCR",
      
       Max( iif(level = 0, _fld9602,     null ) )   Артикул,
       Max( iif(level = 0, _Description, null ) )   Номенклатура,
       Max( iif(level = 0, _Fld9626RRef, null ) )   TradeMarkID
       from V2
       group by source_id
),

V4 AS
(
SELECT V3.*,
       TM._Description                     ТоварнаяМарка
  FROM V3
  LEFT JOIN _Reference188  TM   ON  TM._IDRRef = V3.TradeMarkID AND  TM._Fld675 = 0               
)

SELECT * FROM V4