模拟多级表头的分组统计_sqlserver

原帖地址:
http://community.csdn.net/Expert/topic/3434/3434688.xml?temp=3.246486E-03

–测试数据
create table 单位表 (单位代码 varchar(10),单位名称 varchar(50))

http://www.gaodaima.com/35835.html模拟多级表头的分组统计_sqlserver

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 单位表,供应表

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

欢迎大家阅读《模拟多级表头的分组统计_sqlserver》,跪求各位点评,若觉得好的话请收藏本文，by 搞代码

喜欢 (0)赏

[搞代码]

分享 (0)