求解一复杂问题
已经有数据库表，存放内容示例
id（编号）   compName   addDate   addValue
1                     cmp1           1               100
2                     cmp1           2               332
3                     cmp2           1               546
4                     cmp4           3               450
5                     cmp3           3               1000

....


现求一视图，结果为
编号       公司名称   一月份   二月份   三月份   ...   十二月   总计
1             cmp1           100         332
2             cmp2           无记录   2


.......


------解决方案--------------------
select 公司名称,
sum(case when adddate = 1 then addvalue else 0 end) as '一月份 ',
sum(case when adddate = 2 then addvalue else 0 end) as '二月份 ',
sum(case when adddate = 3 then addvalue else 0 end) as '三月份 ',
sum(case when adddate = 4 then addvalue else 0 end) as '四月份 ',
sum(case when adddate = 5 then addvalue else 0 end) as '五月份 ',
sum(case when adddate = 6 then addvalue else 0 end) as '六月份 ',
sum(case when adddate = 7 then addvalue else 0 end) as '七月份 ',
sum(case when adddate = 8 then addvalue else 0 end) as '八月份 ',
sum(case when adddate = 9 then addvalue else 0 end) as '九月份 ',
sum(case when adddate = 10 then addvalue else 0 end) as '十月份 ',
sum(case when adddate = 11 then addvalue else 0 end) as '十一月份 ',
sum(case when adddate = 12 then addvalue else 0 end) as '十二月份 '
from tb
group by 公司名称
------解决方案--------------------
Create View V_TEST
As
Select
compName As 公司名称,
SUM(Case addDate When 1 Then addValue Else 0 End) As 一月份,
SUM(Case addDate When 2 Then addValue Else 0 End) As 二月份,
SUM(Case addDate When 3 Then addValue Else 0 End) As 三月份,
SUM(Case addDate When 4 Then addValue Else 0 End) As 四月份,
SUM(Case addDate When 5 Then addValue Else 0 End) As 五月份,
SUM(Case addDate When 6 Then addValue Else 0 End) As 六月份,
SUM(Case addDate When 7 Then addValue Else 0 End) As 七月份,
SUM(Case addDate When 8 Then addValue Else 0 End) As 八月份,
SUM(Case addDate When 9 Then addValue Else 0 End) As 九月份,
SUM(Case addDate When 10 Then addValue Else 0 End) As 十月份,
SUM(Case addDate When 11 Then addValue Else 0 End) As 十一月份,
SUM(Case addDate When 12 Then addValue Else 0 End) As 十二月份
From
表
Group By
compName
GO
------解决方案--------------------
行列转换加合计

例如有表A某些人每月消费数据如下：
id data month
001 11 1
001 12 2
001 13 3
001 14 4
001 15 5
001 16 6
001 17 7
001 18 8
001 19 9
001 110 10
001 111 11