SQL语句_group by

create table 表(jgName varchar(10),saleMoney int,saleTime datetime)
insert into 表 select '机构a',100,'2005-12-1'
insert into 表 select '机构b',300,'2005-12-1'
insert into 表 select '机构a',200,'2005-12-2'
insert into 表 select '机构c',100,'2005-12-2'
insert into 表 select '机构b', 50,'2005-12-3'
insert into 表 select '机构a',100,'2005-11-1'
insert into 表 select '机构b',300,'2005-11-1'
insert into 表 select '机构a',200,'2005-10-2'
insert into 表 select '机构c',100,'2005-10-2'
insert into 表 select '机构b', 50,'2005-11-3'
go

/*方法一
select
datepart(mm,saleTime) as '月份/机构',
sum(case jgName when '机构a' then saleMoney else 0 end) as 机构a,
sum(case jgName when '机构b' then saleMoney else 0 end) as 机构b,
sum(case jgName when '机构c' then saleMoney else 0 end) as 机构c
from 表
group by datepart(mm,saleTime)

*/

/*方法二*/
declare @s varchar(8000)
set @s = ''
select @s = @s+',['+jgName+']=sum(case jgName when '''+jgName+''' then saleMoney else 0 end)'
from 表 group by jgName order by jgName

set @s=   'select [月份/机构]=convert(char(7),saleTime,120)'
        + @s
        + ',[合计]=sum(saleMoney)'
        + ' from 表 group by convert(char(7),saleTime,120) order by [月份/机构]'

exec(@s)

drop table 表
go