模拟多级表头的分组统计

测试数据
create table 单位表 (单位代码 varchar(10),单位名称 varchar(50))
insert 单位表 values('01' ,'中国单位') --1级
insert 单位表 values('0101' ,'山东单位') --2级
insert 单位表 values('010101' ,'山东济南单位') --3级
insert 单位表 values('010102' ,'山东青岛单位') --3级
insert 单位表 values('01010201','山东青岛即默单位一') --4级
insert 单位表 values('01010202','山东青岛即默单位二') --4级
insert 单位表 values('0102' ,'山西单位') --2级
insert 单位表 values('010201' ,'山西大同单位') --3级
insert 单位表 values('0103' ,'陕西单位') --2级
insert 单位表 values('010301' ,'陕西西安单位') --3级
--insert 单位表 values('01030101' ,'陕西西安A单位') --3级
--insert 单位表 values('0103010101' ,'陕西西安aa单位') --3级
insert 单位表 values('010302' ,'陕西咸阳单位') --3级

create table 供应表 (物资编号 varchar(10),物资名称 varchar(50),单位代码 varchar(10),供应数量 int)
insert 供应表 values('0001','电子称','010101',1)
insert 供应表 values('0002','电动机','010101',1)
insert 供应表 values('0001','电子称','01010201',1)
insert 供应表 values('0002','电动机','01010201',1)
insert 供应表 values('0001','电子称','010201',1)
insert 供应表 values('0003','电动刷','010201',1)
insert 供应表 values('0004','电动车','010302',1)
go

/*--要求

分级汇总,然后转置得到如下结果:
select '','','','中国单位','中国单位','中国单位','中国单位','中国单位','中国单位','中国单位','中国单位','中国单位'
union all select '','','','山东单位','山东单位','山东单位','山东单位','山西单位','山西单位','陕西单位','陕西单位','陕西单位'
union all select '','','',' ',' ','山东青岛单位','山东青岛单位',' ',' ',' ',' ',' '
union all select '序号','装备名称','合计','小计','山东济南单位','山东青岛即默单位一','山东青岛即默单位二','小计','山西大同单位','小计','陕西西安单位','陕西咸阳单位'
union all select '','总计','7','4','2','2','','2','2','1','','1'
union all select '1','(0001)电子称','3','2','1','1','','1','1','','',''
union all select '2','(0002)电动机','2','2','1','1','','','','','',''
union all select '3','(0003)电动刷','1','','','','','1','1','','',''
union all select '4','(0004)电动车','1','','','','','','','1','','1'

统计结果说明:
1.单位代码固定每两位为1级
2.统计单位表中所有的最末级,如果该单位在供应表中无数据,则对应显示为0
3.小计是根据二级单位合并得到
4.结果中的表头是分级的,一级单位在第一行,二级单位在第二行,如果该单位已经在统计数据,即"序号','装备名称','合计'"这行出现,则不再在对应的级数的表头里面出现
--*/

--查询处理
declare @i varchar(10),@s11 varchar(8000),@s12 varchar(8000),@s13 varchar(8000)
,@s2 varchar(8000),@s3 varchar(8000),@s14 varchar(8000)

select @s11='',@s12='',@s13='',@s14=''
,@s2='',@s3=''

select a=left(单位代码,4),b=left(单位代码,len(单位代码)-2),c=len(单位代码)-2
into # from 单位表 b
where not exists(select * from 单位表 where 单位代码 like b.单位代码+'__')
order by 单位代码

select @i=max(len(b)) from #
while @i>'0'
select @s11=',@'+@i+' varchar(8000)'+@s11
,@s12=',@'+@i+'='''''''''','''''''','''''''''''+@s12
,@s13='
set @=null select @'+@i+'=@'+@i+'+case when @=a then '''' else '
+case when @i>'4' then ''','''''''''' end' else ''',''''''+单位名称+'''''''' end' end
+'+'',''''''+单位名称+'''''''',@=a from '
+case when @i<='4' then '# a,单位表 b where left(a.b,'+@i+')=b.单位代码'
else '(select 单位名称=case when a.c>='+@i+' then 单位名称 else '''' end,a.* from # a,单位表 b where left(a.b,'+@i+')=b.单位代码)a' end
+@s13
,@s14='+'' union all select ''+@'+@i+@s14
,@i=@i-2
select @s12=stuff(@s12,1,1,'')
,@s14=stuff(@s14,1,13,'')

select @s2=@s2+','''+case when len(b.单位代码)=4 then '小计' else b.单位名称 end+''''
,@s3=@s3+case
when len(b.单位代码)=4
then ',['+b.单位名称+'_小计]=cast(sum(case left(单位代码,4) when '''+b.单位代码+''' then 供应数量 else 0 end) as varchar)'
else ',['+b.单位名称+']=cast(sum(case 单位代码 when '''+b.单位代码+''' then 供应数量 else 0 end) as varchar)'
end
from 单位表 b
where len(单位代码)=4
or not exists(select * from 单位表 where 单位代码 like b.单位代码+'__')
order by 单位代码
set @s2=replace(@s2,'''','''''')

exec('
select 序号=cast('''' as varchar(10))
,装备名称=case
when grouping(物资编号)=1 then ''总计''
else ''(''+物资编号+'')''+物资名称 end
,供应数量=cast(sum(供应数量) as varchar)'+@s3+'
into #t
from 供应表
group by 物资编号,物资名称 with rollup
having grouping(物资名称)=0 or grouping(物资编号)=1
order by grouping(物资编号) desc
declare @i int
set @i=-1
update #t set @i=@i+1,序号=case when @i=0 then '''' else cast(@i as varchar) end

declare @ varchar(10)'+@s11+'
select '+@s12+'
'+@s13+'
exec('''+@s14+'+''
union all
select ''''序号'''',''''装备名称'''',''''合计'''''+@s2+'
union all
select * from #t
'')
drop table #
')
go

--删除测试
drop table 单位表,供应表

/*--测试结果(自己看)--*/

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8591181/viewspace-899289/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8591181/viewspace-899289/