已经有数据库表，存放内容示例
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