Skip to content

Instantly share code, notes, and snippets.

@Konctantin
Last active April 17, 2020 05:43
Show Gist options
  • Save Konctantin/83f0b5fec1273ac3183894804e00fc86 to your computer and use it in GitHub Desktop.
Save Konctantin/83f0b5fec1273ac3183894804e00fc86 to your computer and use it in GitHub Desktop.
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment