模拟多级表头的分组统计

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

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

 

原文转自：http://www.ltesting.net