模拟多级表头的分组统计

原帖地址:
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 单位表,供应表

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