求解一复杂问题

[Luckyji]

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



.......

[Luckyji]

上面数据写得有点儿问题，不过大概意思就是说，显示所有的月份的数据，然后把所有公司列出来

[dawugui]

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 公司名称

[paoluo]

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

[dawugui]

行列转换加合计

例如有表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
001 112 12
002 21 1
002 22 2
002 23 3
002 24 4
002 25 5
002 26 6
002 27 7
002 28 8
002 29 9
002 210 10
002 211 11
002 212 12
要实现如下结果：
人员 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
001 11 12 13 14 15 16 17 18 19 110 111 112
002 21 22 23 24 25 26 27 28 29 210 211 212

create table 表A
(
id char(3),
data int,
month int
)
insert into 表A(id,data,month) values('001',11,1)
insert into 表A(id,data,month) values('001',12,2)
insert into 表A(id,data,month) values('001',13,3)
insert into 表A(id,data,month) values('001',14,4)
insert into 表A(id,data,month) values('001',15,5)
insert into 表A(id,data,month) values('001',16,6)
insert into 表A(id,data,month) values('001',17,7)
insert into 表A(id,data,month) values('001',18,8)
insert into 表A(id,data,month) values('001',19,9)
insert into 表A(id,data,month) values('001',110,10)
insert into 表A(id,data,month) values('001',111,11)
insert into 表A(id,data,month) values('001',112,12)
insert into 表A(id,data,month) values('002',21,1)
insert into 表A(id,data,month) values('002',22,2)
insert into 表A(id,data,month) values('002',23,3)
insert into 表A(id,data,month) values('002',24,4)
insert into 表A(id,data,month) values('002',25,5)
insert into 表A(id,data,month) values('002',26,6)
insert into 表A(id,data,month) values('002',27,7)
insert into 表A(id,data,month) values('002',28,8)
insert into 表A(id,data,month) values('002',29,9)
insert into 表A(id,data,month) values('002',210,10)
insert into 表A(id,data,month) values('002',211,11)
insert into 表A(id,data,month) values('002',212,12)
go

SELECT id as '人员' ,
SUM(CASE month WHEN 1 THEN data ELSE 0 END) AS '1月' ,
SUM(CASE month WHEN 2 THEN data ELSE 0 END) AS '2月' ,
SUM(CASE month WHEN 3 THEN data ELSE 0 END) AS '3月' ,
SUM(CASE month WHEN 4 THEN data ELSE 0 END) AS '4月' ,
SUM(CASE month WHEN 5 THEN data ELSE 0 END) AS '5月' ,
SUM(CASE month WHEN 6 THEN data ELSE 0 END) AS '6月' ,
SUM(CASE month WHEN 7 THEN data ELSE 0 END) AS '7月' ,
SUM(CASE month WHEN 8 THEN data ELSE 0 END) AS '8月' ,
SUM(CASE month WHEN 9 THEN data ELSE 0 END) AS '9月' ,
SUM(CASE month WHEN 10 THEN data ELSE 0 END) AS '10月' ,
SUM(CASE month WHEN 11 THEN data ELSE 0 END) AS '11月' ,
SUM(CASE month WHEN 12 THEN data ELSE 0 END) AS '12月'
FROM 表A
GROUP BY ID

drop table 表A

人员 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
---- --- --- --- --- --- --- --- --- --- ---- ---- ----
001 11 12 13 14 15 16 17 18 19 110 111 112
002 21 22 23 24 25 26 27 28 29 210 211 212

（所影响的行数为 2 行）

合计每个人每年的数据
人员 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 合计
001 11 12 13 14 15 16 17 18 19 110 111 112 468
002 21 22 23 24 25 26 27 28 29 210 211 212 858

SELECT id as '人员' ,
SUM(CASE month WHEN 1 THEN data ELSE 0 END) AS '1月' ,
SUM(CASE month WHEN 2 THEN data ELSE 0 END) AS '2月' ,
SUM(CASE month WHEN 3 THEN data ELSE 0 END) AS '3月' ,
SUM(CASE month WHEN 4 THEN data ELSE 0 END) AS '4月' ,
SUM(CASE month WHEN 5 THEN data ELSE 0 END) AS '5月' ,
SUM(CASE month WHEN 6 THEN data ELSE 0 END) AS '6月' ,
SUM(CASE month WHEN 7 THEN data ELSE 0 END) AS '7月' ,
SUM(CASE month WHEN 8 THEN data ELSE 0 END) AS '8月' ,
SUM(CASE month WHEN 9 THEN data ELSE 0 END) AS '9月' ,
SUM(CASE month WHEN 10 THEN data ELSE 0 END) AS '10月' ,
SUM(CASE month WHEN 11 THEN data ELSE 0 END) AS '11月' ,
SUM(CASE month WHEN 12 THEN data ELSE 0 END) AS '12月' ,
SUM(data) as '合计'
FROM 表A
GROUP BY ID

人员 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 合计
---- --- --- --- --- --- --- --- --- --- ---- ---- ---- ----
001 11 12 13 14 15 16 17 18 19 110 111 112 468
002 21 22 23 24 25 26 27 28 29 210 211 212 858

[paoluo]

如果不需要第一列，就可以像上面那麼寫。

如果需要第一列就麻煩些了。

[dawugui]

--也不麻烦,插入临时表

select id = identity(int,1,1),* into test from
(
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 公司名称
) t

select * from test

[Luckyji]

多谢各位老鸟，俺试一下，尽快给分

[paoluo]

實現這個效果是不麻煩，但是就是要借用下臨時表，另外，樓主要的是“一视图”。

第一列可以沒有必要加上，直接用上面的語句實現吧。

[dawugui]

--也不麻烦,插入临时表

select 编号 = identity(int,1,1),* into test from
(
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 '十二月份',
sum(addvalue) as 总计
from tb
group by 公司名称
) t

select * from test

[paoluo]

多回復還是有好處。 ：