测试数据:
create table hl(name char(22),dated datetime,shuliang int)
insert into hlselect 'swz','2011-04-4',12 union all
select 'swz','2011-04-4',23 union all
select 'swz','2011-06-4',12 union all
select 'swz','2011-04-4',12 union all
select 'cl','2011-04-4',134 union all
select 'cl','2011-03-4',134 union all
select 'cl','2011-09-4',134
select name 姓名,
sum(case datepart(mm,dated) when '1' then shuliang else 0 end) 一月份,
sum(case datepart(mm,dated) when '2' then shuliang else 0 end) 二月份,
sum(case datepart(mm,dated) when '3' then shuliang else 0 end) 三月份,
sum(case datepart(mm,dated) when '4' then shuliang else 0 end) 四月份,
sum(case datepart(mm,dated) when '5' then shuliang else 0 end) 五月份,
sum(case datepart(mm,dated) when '6' then shuliang else 0 end) 六月份,
sum(case datepart(mm,dated) when '7' then shuliang else 0 end) 七月份,
sum(case datepart(mm,dated) when '8' then shuliang else 0 end) 八月份,
sum(case datepart(mm,dated) when '9' then shuliang else 0 end) 九月份,
sum(case datepart(mm,dated) when '10' then shuliang else 0 end) 十月份,
sum(case datepart(mm,dated) when '11' then shuliang else 0 end) 十一月份,
sum(case datepart(mm,dated) when '12' then shuliang else 0 end) 十二月份,
sum(shuliang) as 总量
from hl
group by name
--静态的
declare @sql varchar(8000)
set @sql = 'select Name as ' + '姓名'
select @sql = @sql + ' , max(case cast(datepart(mm,dated) as char(5)) when ''' + cast(datepart(mm,dated) as char(5)) + ''' then shuliang else 0 end) [' + cast(datepart(mm,dated) as char(7)) + ']'
from (select distinct dated from hl) as a
set @sql = @sql + ' from hl group by name'
exec(@sql)
-动态的其实看不懂