典型的按月做行列转化

有一张表Product数据有
ProductName Total inDate
Product1 1 2012-01-01 00:00:00.000
Product2 2 2012-01-23 00:00:00.000
Product3 3 2012-01-14 00:00:00.000
Product1 3 2012-02-24 00:00:00.000
Product2 2 2012-02-04 00:00:00.000
Product3 1 2012-02-29 00:00:00.000
Product1 10 2012-06-06 00:00:00.000
Product2 10 2012-06-16 00:00:00.000
Product3 10 2012-06-26 00:00:00.000
Product3 100 2012-06-16 00:00:00.000

要求得出的结果是：
产品名称 1月 2月 6月
product1 1 3 10
product2 2 2 10
product3 3 1 110

 

declare @sql varchar(8000)

set @sql = 'select ProductName 产品名称'select @sql = @sql + ' ,max(case CONVERT(varchar(7),inDate,120) when ''' +dm+ ''' then Total else 0 end) as ['+dm+']'from (select distinct CONVERT(varchar(7),inDate,120) as dm from Product) as a

set @sql = @sql + ' from Product group by ProductName'

exec(@sql)