declare @s varchar(8000)
set @s = 'select vtweg=isnull(vtweg,''合计'')'select @s=@s+',['+vkbur+']=sum(case vkbur when '''+vkbur+''' then mete else 0 end)'
from TABLEA group by vkburset @s=@s+' 合计=sum(mete) from TABLEA group by vtweg with rollup'
set @s = 'select vtweg=isnull(vtweg,''合计'')'select @s=@s+',['+vkbur+']=sum(case vkbur when '''+vkbur+''' then mete else 0 end)'
from TABLEA group by vkburset @s=@s+' 合计=sum(mete) from TABLEA group by vtweg with rollup'
insert into TABLEA select rtrim('GT '),'上海',5
insert into TABLEA select rtrim('GT '),'南京',6
insert into TABLEA select rtrim('KA '),'上海',7
insert into TABLEA select rtrim('KA '),'南京',7
insert into TABLEA select rtrim('AFH'),'上海',7
insert into TABLEA select rtrim('AFH'),'南京',8declare @s varchar(8000)
set @s = 'select vtweg=isnull(vtweg,''合计'')'
select @s=@s+',['+vkbur+']=sum(case vkbur when '''+vkbur+''' then mete else 0 end)'
from TABLEA group by vkbur
set @s=@s+',合计=sum(mete) from TABLEA group by vtweg with rollup'
exec(@s)drop table TABLEA
vtweg=isnull(vtweg,'合计'),
[南京]=sum(case vkbur when '南京' then mete else 0 end),
[上海]=sum(case vkbur when '上海' then mete else 0 end),
...,
合计=sum(mete)
from
TABLEA
group by
vtweg with rollup
insert into a select 'GT','上海',5
insert into a select 'GT','南京',6
insert into a select 'KA','上海',7
insert into a select 'KA','南京',7
insert into a select 'AFH','上海',7
insert into a select 'AFH','南京',8
declare
@sql varchar(4000)
set @sql=''
select @sql=@sql+','+vkbur+'=sum(case when vkbur='''+vkbur+''' then mete else 0 end)'
from a
group by vkbur
set @sql='select vtweg'+@sql+' from a group by vtweg'
exec (@sql)
你少了条件了!!我还要分GT,KA,AFH
insert into a select 'GT','上海',5
insert into a select 'GT','南京',6
insert into a select 'KA','上海',7
insert into a select 'KA','南京',7
insert into a select 'AFH','上海',7
insert into a select 'AFH','南京',8
declare
@sql varchar(4000)
set @sql=''
select @sql=@sql+','+vkbur+'=sum(case when vkbur='''+vkbur+''' then mete else 0 end)'
from a
group by vkbur
set @sql='select vtweg=case when grouping(vtweg)=0 then vtweg
when grouping(vtweg)=1 then ''合计'' else '''' end'+@sql+' from a group by vtweg with rollup'
exec (@sql)vtweg 南京 上海
---------- ----------- -----------
AFH 8 7
GT 6 5
KA 7 7
合计 21 19(4 行受影响)
你少了条件了!!我还要分GT,KA,AFH
--------------------------------------------------------
都已经Group by vtweg了,不需要再区分了吧?
when grouping(vtweg)=1 then '合计' else '' end,
南京=sum(case when vkbur='南京' then mete else 0 end),
上海=sum(case when vkbur='上海' then mete else 0 end),
合计=sum(case when vkbur='南京' then mete else 0 end)
+sum(case when vkbur='上海' then mete else 0 end)
from a group by vtweg with rollupvtweg 南京 上海 合计
---------- ----------- ----------- -----------
AFH 8 7 15
GT 6 5 11
KA 7 7 14
合计 21 19 40(4 行受影响)
insert into a select 'GT','上海',5
insert into a select 'GT','南京',6
insert into a select 'KA','上海',7
insert into a select 'KA','南京',7
insert into a select 'AFH','上海',7
insert into a select 'AFH','南京',8
declare
@sql varchar(4000),
@sq varchar(4000),
@s varchar(4000),
@i int
set @sql=''
set @s=''
select @sql=@sql+','+vkbur+'=sum(case when vkbur='''+vkbur+''' then mete else 0 end)'
from a
group by vkbur
set @i=len(@sql)/46
set @sq=@sql
while (@i>1)
begin
set @s=@s+right(@sq,46)+'+'
set @sq=left(@sq,50)
set @i=len(@sq)/46
end
set @s=@s+right(@sq,46)set @sql='select vtweg=case when grouping(vtweg)=0 then vtweg
when grouping(vtweg)=1 then ''合计'' else '''' end'+@sql+',合计='+@s+' from a group by vtweg with rollup'
exec (@sql)全自动加字段计算过程,好了吧