with tree as(
select ElementID as CSKUId, ElementID, ParentId, PGCode, [level], NameRus, ExtBrandId
from v_Goods_Elements_Work
where [level]=7
union all
select	A.CSKUId, dt.ElementID, dt.ParentId, dt.PGCode, dt.[level], dt.NameRus, dt.ExtBrandId
from tree as A
	inner join v_Goods_Elements_Work as dt on a.ParentID=dt.ElementID
	
)

select CSKUId, ElementID, ParentId, PGCode, [level], NameRus, ExtBrandId
from tree [/SQL]

Level = {-50,-40...6,7}. 

Строится данная VIEWшка из ещё оной, но там просто

[SRC SQL]CREATE VIEW v_Goods_Elements_Work AS
SELECT TOP 100 PERCENT FROM General..t_Goods_Element

CREATE VIEW [dbo].[v_Goods_Elements_Work] AS
SELECT	TOP(100) PERCENT *
FROM	General.dbo.t_Goods_Element
GO

SELECT     TOP (100) PERCENT ElementID, ParentID, PGCode, NameRus, NameEng, ShortName, [Level], ExtBrandID, Position,PositionPG
FROM         General.dbo.t_Goods_Elements AS ge
ORDER BY ElementID, [Level]

         declare @w table(CSKUId bigint)
         insert @w values (10002692)
         insert @w values (10002735)
         insert @w values (10003174)
         insert @w values (10013094)
         insert @w values (10015218)
         insert @w values (10002692)
         insert @w values (10002735)
         insert @w values (10003174)
         insert @w values (10013094)
         insert @w values (10018759)
         insert @w values (10010785)
--SET STATISTICS TIME ON
select TOP 1 bc.ItemId
  from @w w
          inner join v_GoodsTreeElements_test/*новая*/ t with(nolock) on w.CSKUId = t.CSKUId 
          inner join v_BrandCo bc with(nolock) on t.ExtBrandId = bc.ItemId
     where t.Level = 1
     order by bc.Position

--SET STATISTICS TIME OFF
GO