select * from ( select cast(vendor as varchar(10)) as vendor ,sum(case when depart='01' then money else 0 end) [01] ,sum(case when depart='02' then money else 0 end) [02] ,sum(case when depart='03' then money else 0 end) [03] ,sum(case when depart='04' then money else 0 end) [04] ,sum(case when depart='05' then money else 0 end) [05] ,sum(money) as 合计 ,0 as ordercol from 表 group by vendor union all select '合计:' ,sum(case when depart='01' then money else 0 end) [01] ,sum(case when depart='02' then money else 0 end) [02] ,sum(case when depart='03' then money else 0 end) [03] ,sum(case when depart='04' then money else 0 end) [04] ,sum(case when depart='05' then money else 0 end) [05] ,sum(money) as 合计 ,1 as ordercol from 表 ) temp order by ordercol,vendor
SELECT vendor, depart, SUM(money) AS Expr1 FROM test GROUP BY vendor, depart WITH CUBE 然后再作相应处理纪珂(比如上面的作为视图)
SELECT vendor, depart, SUM(money) AS Expr1 FROM test GROUP BY vendor, depart WITH ROLLUP 最好
同意: select * from ( select cast(vendor as varchar(10)) as vendor ,sum(case when depart='01' then money else 0 end) [01] ,sum(case when depart='02' then money else 0 end) [02] ,sum(case when depart='03' then money else 0 end) [03] ,sum(case when depart='04' then money else 0 end) [04] ,sum(case when depart='05' then money else 0 end) [05] ,sum(money) as 合计 ,0 as ordercol from 表 group by vendor union all select '合计:' ,sum(case when depart='01' then money else 0 end) [01] ,sum(case when depart='02' then money else 0 end) [02] ,sum(case when depart='03' then money else 0 end) [03] ,sum(case when depart='04' then money else 0 end) [04] ,sum(case when depart='05' then money else 0 end) [05] ,sum(money) as 合计 ,1 as ordercol from 表 ) temp order by ordercol,vendor
vendor depart money -------------- ---------- 1 01 100 declare @sql varchar(8000) set @sql = 'select vendor' select @sql = @sql + ',sum(case vendor when '''+vendor+''' then Money)as '+vendor+',sum(Money) as '合计'' from (select distinct Vender from mytable) as a set @sql=@sql+' from yourtable group by Vendor'exec(@sql)
create table #aa (vendor varchar(10),depart varchar(10),money int) insert into #aa values(1,'01',100) insert into #aa values(1,'02',200) insert into #aa values(1,'03',300) insert into #aa values(1,'04',400) insert into #aa values(1,'05',500) insert into #aa values(2,'01',100) insert into #aa values(2,'02',100) insert into #aa values(2,'03',200) insert into #aa values(2,'04',200) insert into #aa values(2,'05',300)select vendor,max(case depart when '01' then money end) [01], max(case depart when '02' then money end) [02], max(case depart when '03' then money end) [03], max(case depart when '04' then money end) [04], max(case depart when '05' then money end) [05], sum(money) as '小计' into #a from #aa group by vendor select * from #a union all select '合计', cast(sum([01]) as varchar(1000)), cast(sum([02]) as varchar(1000)), cast(sum([03]) as varchar(1000)), cast(sum([04]) as varchar(1000)), cast(sum([05]) as varchar(1000)), cast(sum(小计) as varchar(100)) from #adrop table #aa,#a
create table #aa (vendor varchar(10),depart varchar(10),money int) insert into #aa values(1,'01',100) insert into #aa values(1,'02',200) insert into #aa values(1,'03',300) insert into #aa values(1,'04',400) insert into #aa values(1,'05',500) insert into #aa values(2,'01',100) insert into #aa values(2,'02',100) insert into #aa values(2,'03',200) insert into #aa values(2,'04',200) insert into #aa values(2,'05',300)declare @sql varchar(1000),@i int,@sql2 varchar(1000) select @i=0,@sql='select vendor',@sql2='' select @i=@i+1,@sql = @sql + ',max(case depart when ''0'+convert(varchar,@i)+''' then money end) [0'+cast(@i as varchar(10))+']' ,@sql2=@sql2+',cast(sum([0'+cast(@i as varchar(10))+']) as varchar(1000))' from #aaselect @sql=@sql+' ,sum(money) as ''小计'' into #a from #aa group by vendor',@sql2=@sql2+',cast(sum(小计) as varchar(100))' exec (@sql+ ' select * from #a union all select ''合计'''+@sql2+' from #a')drop table #aa
create table #aa (vendor varchar(10),depart varchar(10),money int) insert into #aa values(1,'01',100) insert into #aa values(1,'02',200) insert into #aa values(1,'03',300) insert into #aa values(1,'04',400) insert into #aa values(1,'05',500) insert into #aa values(2,'01',100) insert into #aa values(2,'02',100) insert into #aa values(2,'03',200) insert into #aa values(2,'04',200) insert into #aa values(2,'05',300)declare @sql varchar(1000),@i int,@sql2 varchar(1000),@count int select @i=1,@sql='select vendor',@sql2='' select @count=count(distinct depart) from #aa while @i <= @count begin set @sql = @sql + ',max(case depart when ''0'+convert(varchar,@i)+''' then money end) [0'+cast(@i as varchar(10))+']' set @sql2 = @sql2+',cast(sum([0'+cast(@i as varchar(10))+']) as varchar(1000))' set @i = @i +1 endselect @sql=@sql+' ,sum(money) as ''小计'' into #a from #aa group by vendor',@sql2=@sql2+',cast(sum(小计) as varchar(100))' exec (@sql+ ' select * from #a union all select ''合计'''+@sql2+' from #a')drop table #aa
select cast(vendor as varchar(10)) as vendor
,sum(case when depart='01' then money else 0 end) [01]
,sum(case when depart='02' then money else 0 end) [02]
,sum(case when depart='03' then money else 0 end) [03]
,sum(case when depart='04' then money else 0 end) [04]
,sum(case when depart='05' then money else 0 end) [05]
,sum(money) as 合计
,0 as ordercol
from 表 group by vendor
union all
select '合计:'
,sum(case when depart='01' then money else 0 end) [01]
,sum(case when depart='02' then money else 0 end) [02]
,sum(case when depart='03' then money else 0 end) [03]
,sum(case when depart='04' then money else 0 end) [04]
,sum(case when depart='05' then money else 0 end) [05]
,sum(money) as 合计
,1 as ordercol
from 表
) temp order by ordercol,vendor
FROM test
GROUP BY vendor, depart WITH CUBE
然后再作相应处理纪珂(比如上面的作为视图)
FROM test
GROUP BY vendor, depart WITH ROLLUP
最好
select * from (
select cast(vendor as varchar(10)) as vendor
,sum(case when depart='01' then money else 0 end) [01]
,sum(case when depart='02' then money else 0 end) [02]
,sum(case when depart='03' then money else 0 end) [03]
,sum(case when depart='04' then money else 0 end) [04]
,sum(case when depart='05' then money else 0 end) [05]
,sum(money) as 合计
,0 as ordercol
from 表 group by vendor
union all
select '合计:'
,sum(case when depart='01' then money else 0 end) [01]
,sum(case when depart='02' then money else 0 end) [02]
,sum(case when depart='03' then money else 0 end) [03]
,sum(case when depart='04' then money else 0 end) [04]
,sum(case when depart='05' then money else 0 end) [05]
,sum(money) as 合计
,1 as ordercol
from 表
) temp order by ordercol,vendor
-------------- ----------
1 01 100
declare @sql varchar(8000)
set @sql = 'select vendor'
select @sql = @sql + ',sum(case vendor when '''+vendor+''' then Money)as '+vendor+',sum(Money) as '合计''
from (select distinct Vender from mytable) as a
set @sql=@sql+' from yourtable group by Vendor'exec(@sql)
insert into #aa values(1,'01',100)
insert into #aa values(1,'02',200)
insert into #aa values(1,'03',300)
insert into #aa values(1,'04',400)
insert into #aa values(1,'05',500)
insert into #aa values(2,'01',100)
insert into #aa values(2,'02',100)
insert into #aa values(2,'03',200)
insert into #aa values(2,'04',200)
insert into #aa values(2,'05',300)select vendor,max(case depart when '01' then money end) [01],
max(case depart when '02' then money end) [02],
max(case depart when '03' then money end) [03],
max(case depart when '04' then money end) [04],
max(case depart when '05' then money end) [05],
sum(money) as '小计' into #a from #aa group by vendor select * from #a union all select '合计',
cast(sum([01]) as varchar(1000)),
cast(sum([02]) as varchar(1000)),
cast(sum([03]) as varchar(1000)),
cast(sum([04]) as varchar(1000)),
cast(sum([05]) as varchar(1000)),
cast(sum(小计) as varchar(100)) from #adrop table #aa,#a
insert into #aa values(1,'01',100)
insert into #aa values(1,'02',200)
insert into #aa values(1,'03',300)
insert into #aa values(1,'04',400)
insert into #aa values(1,'05',500)
insert into #aa values(2,'01',100)
insert into #aa values(2,'02',100)
insert into #aa values(2,'03',200)
insert into #aa values(2,'04',200)
insert into #aa values(2,'05',300)declare @sql varchar(1000),@i int,@sql2 varchar(1000)
select @i=0,@sql='select vendor',@sql2=''
select @i=@i+1,@sql = @sql + ',max(case depart when ''0'+convert(varchar,@i)+''' then money end) [0'+cast(@i as varchar(10))+']'
,@sql2=@sql2+',cast(sum([0'+cast(@i as varchar(10))+']) as varchar(1000))' from #aaselect @sql=@sql+' ,sum(money) as ''小计'' into #a from #aa group by vendor',@sql2=@sql2+',cast(sum(小计) as varchar(100))'
exec (@sql+ ' select * from #a union all select ''合计'''+@sql2+' from #a')drop table #aa
insert into #aa values(1,'01',100)
insert into #aa values(1,'02',200)
insert into #aa values(1,'03',300)
insert into #aa values(1,'04',400)
insert into #aa values(1,'05',500)
insert into #aa values(2,'01',100)
insert into #aa values(2,'02',100)
insert into #aa values(2,'03',200)
insert into #aa values(2,'04',200)
insert into #aa values(2,'05',300)declare @sql varchar(1000),@i int,@sql2 varchar(1000),@count int
select @i=1,@sql='select vendor',@sql2=''
select @count=count(distinct depart) from #aa
while @i <= @count
begin
set @sql = @sql + ',max(case depart when ''0'+convert(varchar,@i)+''' then money end) [0'+cast(@i as varchar(10))+']'
set @sql2 = @sql2+',cast(sum([0'+cast(@i as varchar(10))+']) as varchar(1000))'
set @i = @i +1
endselect @sql=@sql+' ,sum(money) as ''小计'' into #a from #aa group by vendor',@sql2=@sql2+',cast(sum(小计) as varchar(100))'
exec (@sql+ ' select * from #a union all select ''合计'''+@sql2+' from #a')drop table #aa