表结构是这样的
id code money useNum date
1 000100020001 10 10 2014/10/10
2 000100020001 20 20 2014/11/10
3 000100020001 30 30 2014/12/10
4 000100010001 5 5 2014/10/10
5 000100010001 15 15 2014/11/10
6 000100010001 25 25 2014/12/10
想得到用电量结果
code 10月 11月 12月
000100010001 5.0 15.0 25.0
000100020001 10.0 20.0 30.0
因为没有其他月份，所以1到9月不显示
写了如下sql语句
declare @sql varchar(2000)
declare @mon varchar(2)
set @sql = 'select code'
select @sql = @sql + ',sum(case '''+mon+''' when '''+mon+''' then useNum end) ['+mon+'月]'
from (select distinct substring(convert(varchar(7),date,120),6,2) mon from money) as a select
@sql = @sql+' from money group by code'exec(@sql)
但显示的结果却是
code 10月 11月 12月
000100010001 45.0 45.0 45.0
000100020001 60.0 60.0 60.0
请问怎么修改这条语句
------解决思路----------------------
declare @sql varchar(2000)
set @sql = 'select code'
select @sql = @sql + ',sum(case MONTH(date) when '''+CAST(number AS VARCHAR(10))+''' then useNum end) ['+CAST(number AS VARCHAR(10))+'月]'
from master..spt_values
WHERE type='P'AND number>0 AND number<=12
SET @sql = @sql+' from money group by code,YEAR(date)'
PRINT @sql
exec(@sql)