Код

запросODBC = СоздатьОбъект("ODBCRecordSet");
текстЗапроса = "
|set nocount on
|;
|select
|  рег.Номенклатура as [Товар]
|  , товары.parentid as [Родитель]
|  , товары.descr as [Наименование]
|  , рег.КоличествоОстаток as [Остаток]
|into #Остатки
|from
|  $РегистрОстатки.ОстаткиТМЦ(,,,(Номенклатура),(Количество)) as рег
|  join $Справочник.Номенклатура as товары with (nolock)
|  on рег.Номенклатура = товары.id
|;
|
|with ДеревоГрупп as (
|select
|  cast($ПустойИД as char(9)) as [Группа]
|  , 0 as [Уровень]
|  , cast('' as varchar(200)) as [Наименование]
|  , cast('' as varchar(500)) as [Порядок]
|
|union all
|
|select
|  товары.id
|  , ДеревоГрупп.Уровень + 1
|  , cast(REPLICATE('   ', ДеревоГрупп.Уровень) + товары.descr as varchar(200))
|  , cast(ДеревоГрупп.Порядок + '|' + товары.descr + товары.id as varchar(500))
|from
|  $Справочник.Номенклатура as товары with (nolock,forceseek,index(pcode))
|  join ДеревоГрупп on товары.parentid = ДеревоГрупп.Группа
|where
|  товары.isfolder = 1
|)
|
|, итогиГрупп as (
|select
|  Остатки.Родитель as [Группа]
|  , min(товары.parentid) as [Родитель]
|  , sum(Остатки.Остаток) as [Остаток]
|from
|  #Остатки as Остатки
|  join $Справочник.Номенклатура as товары with (nolock)
|  on Остатки.Родитель = товары.id
|group by Остатки.Родитель
|
|union all
|
|select
|  родители.id
|  , родители.parentid
|  , итогиГрупп.Остаток
|from
|  $Справочник.Номенклатура as родители with (nolock)
|  join итогиГрупп on родители.id = итогиГрупп.Родитель
|)
|
|select
|  ДеревоГрупп.Уровень as [Уровень $Число]
|  , ДеревоГрупп.Группа as [Родитель $Справочник.Номенклатура]
|  , ДеревоГрупп.Наименование as [Родитель_Наименование]
|  , группыОстатков.Остаток as [Родитель_Остаток $Число]
|  , Остатки.Наименование as [Товар_Наименование]
|  , Остатки.Товар as [Товар $Справочник.Номенклатура]
|  , Остатки.Остаток as [Товар_Остаток $Число]
|from
|  ДеревоГрупп
|  join (
|    select
|      итогиГрупп.Группа as [Группа]
|      , sum(итогиГрупп.Остаток) as [Остаток]
|    from
|      итогиГрупп
|    group by
|      итогиГрупп.Группа
|   ) as группыОстатков on ДеревоГрупп.Группа = группыОстатков.Группа
|  left join #Остатки as Остатки on группыОстатков.Группа = Остатки.Родитель
|order by
|  ДеревоГрупп.Порядок, Остатки.Наименование, Остатки.Товар
|;
|
|drop table #Остатки
|;
|
|set nocount off
|";
тзРезультат = запросODBC.ВыполнитьИнструкцию(текстЗапроса);
//тзРезультат // : ТаблицаЗначений
 

Код

запросODBC.Выполнить("
|CREATE OR ALTER FUNCTION dbo.ДеревоНоменклатуры()
|RETURNS TABLE
|RETURN (
|with ДеревоГрупп as (
|select
|  cast($ПустойИД as char(9)) as [Группа]
|  , 0 as [Уровень]
|  , cast('' as char(80)) as [Наименование]
|  , cast('' as varchar(500)) as [Порядок]
|
|union all
|
|select
|  товары.id
|  , ДеревоГрупп.Уровень + 1
|  , товары.descr
|  , cast(ДеревоГрупп.Порядок + '|' + товары.descr + товары.id as varchar(500))
|from
|  $Справочник.Номенклатура as товары with (nolock,forceseek,index(pcode))
|  join ДеревоГрупп on товары.parentid = ДеревоГрупп.Группа
|where
|  товары.isfolder = 1
|)
|
|select
|  Группа, Наименование, Уровень, row_number() over (order by Порядок) as Порядок
|from
|  ДеревоГрупп
|)
|");
 

Код

select * from ДеревоНоменклатуры() order by порядок