分组|统计
原帖地址:
http://community.csdn.net/Expert/topic/3434/3434688.xml?temp=3.246486E-03
--测试数据
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 单位表,供应表
/*--测试结果(自己看)--*/
模拟多级表头的分组统计
80酷酷网 80kuku.com
