sel_grouping dynamicly.sql

declare @fldList table(nm varchar(128));
select drv.IdZap
    , drv.dorogaid
    , dor.MNKD as DorName
    , Depo_Rem
    , fosv.SNAME as Depo_RemName
    , vag_GR
    , Or_Pr
    , Prich_Otkl
    , isnull(fosv.CDI,0) as CDI
    , isnull(fosv.VRK,0) as VRK
    , OnPaper
    , Nom_Vag
    , Date53
    , case isnull(fosv.Vrk,0) when 0 then case when isnull(fosv.CDI,0)=1 then 4 else 0 end
						  else fosv.Vrk end as VRK_tt
	, case isnull(fosv.Vrk,0) when 0 then case when isnull(fosv.CDI,0)=1 then 'ЦДИ' else 'Прочие' end
						  when 1 then 'ВРК-1'
						  when 2 then 'ВРК-2'
						  when 3 then 'ВРК-3' end as VRKName
into #tempDr
from kcmod.dbo.DRVagTR drv 
left join kcmod.dbo.nsTovPr_ForOsv fosv ON drv.Depo_Rem = fosv.Vrp_Int
left join kcmod.dbo.nsDoroga dor ON dor.DorogaID = drv.DorogaId 
where Date_Rem between '20200101' and '20200201'

insert into @fldList (nm)
select distinct vag_GR from #tempDr
declare @fldSel varchar(max);
set @fldSel = '';
select @fldSel = @fldSel + ', sum(case when vag_GR = '''+nm+''' then 1 end) as [fld_'+nm+']'
from @fldList

select nm as [Title], 'fld_'+nm as [FldName] from @fldList;

execute('
select DorogaID as id
    , null as parentId
    , DorName as [Name]
    , dorogaid as DorogaID
    , null as [VRK]
    , null as [VRP]
    , null as [SNG]
    , count(*) as [Kol]
    '+@fldSel+'
from #tempDr
group by DorName, dorogaid

union all

select DorogaID + ''_'' + cast(VRK_tt as varchar) as id
    , DorogaID as parentId
    , VRKName as [Name]
    , dorogaid as DorogaID
    , VRK_tt as [VRK]
    , null as [VRP]
    , null as [SNG]
    , count(*) as [Kol]
    '+@fldSel+'
from #tempDr
group by DorName, dorogaid, VRK_tt, VRKName

union all

select DorogaID + ''_'' + cast(VRK_tt as varchar) + ''_'' + cast(Depo_Rem as varchar) as id
    , DorogaID + ''_'' + cast(VRK_tt as varchar) as parentId
    , Depo_RemName as [Name]
    , dorogaid as DorogaID
    , VRK_tt as [VRK]
    , Depo_Rem as [VRP]
    , null as [SNG]
    , count(*) as [Kol]
    '+@fldSel+'
from #tempDr
group by DorName, dorogaid, VRK_tt, VRKName, Depo_Rem, Depo_RemName

union all

select ''99999998'' as id
    , null as parentId
    , ''ИТОГО ВРК'' as [Name]
    , null as DorogaID
    , null as [VRK]
    , null as [VRP]
    , null as [SNG]
    , count(*) as [Kol]
    '+@fldSel+'
from #tempDr
where VRK_tt in (1,2,3)

union all

select ''99999999'' as id
    , null as parentId
    , ''ИТОГО'' as [Name]
    , null as DorogaID
    , null as [VRK]
    , null as [VRP]
    , null as [SNG]
    , count(*) as [Kol]
    '+@fldSel+'
from #tempDr

order by id
')

drop table #tempDr