表
费用名称 费用月份 费用水费 1 100
水费 2 200
水费 3 300
水费 4 500
水费 5 700
...
水费 12 700需要查询出如下结果
费用名称 一月份 二月份 ... 十二月份
水费 100 200 700请问SQL怎么写?
费用名称 费用月份 费用水费 1 100
水费 2 200
水费 3 300
水费 4 500
水费 5 700
...
水费 12 700需要查询出如下结果
费用名称 一月份 二月份 ... 十二月份
水费 100 200 700请问SQL怎么写?
费用名称,
SUM(Case 费用月份 When 1 Then 费用 Else 0 End) As [1月份],
SUM(Case 费用月份 When 2 Then 费用 Else 0 End) As [2月份],
SUM(Case 费用月份 When 3 Then 费用 Else 0 End) As [3月份],
SUM(Case 费用月份 When 4 Then 费用 Else 0 End) As [4月份],
SUM(Case 费用月份 When 5 Then 费用 Else 0 End) As [5月份],
SUM(Case 费用月份 When 6 Then 费用 Else 0 End) As [6月份],
SUM(Case 费用月份 When 7 Then 费用 Else 0 End) As [7月份],
SUM(Case 费用月份 When 8 Then 费用 Else 0 End) As [8月份],
SUM(Case 费用月份 When 9 Then 费用 Else 0 End) As [9月份],
SUM(Case 费用月份 When 10 Then 费用 Else 0 End) As [10月份],
SUM(Case 费用月份 When 11 Then 费用 Else 0 End) As [11月份],
SUM(Case 费用月份 When 12 Then 费用 Else 0 End) As [12月份]
From 表
Group By 费用名称
Set @S=''
Select @S=@S+ ',['+费用月份+'月份]= SUM(Case 费用月份 When '''+费用月份+''' Then 费用 Else 0 End) ' From 表 Order By 费用月份
Set @S='Select 费用名称'+@S+' From 表 Group By 费用名称 Order By 费用名称'
EXEC (@S)
GO
select
费用名称,
sum(case when 费用月份=1 then 费用 else 0 end) as 一月份,
sum(case when 费用月份=3 then 费用 else 0 end) as 二月份,
sum(case when 费用月份=3 then 费用 else 0 end) as 三月份,
sum(case when 费用月份=4 then 费用 else 0 end) as 四月份,
sum(case when 费用月份=5 then 费用 else 0 end) as 五月份,
sum(case when 费用月份=6 then 费用 else 0 end) as 六月份,
sum(case when 费用月份=7 then 费用 else 0 end) as 七月份,
sum(case when 费用月份=8 then 费用 else 0 end) as 八月份,
sum(case when 费用月份=9 then 费用 else 0 end) as 九月份,
sum(case when 费用月份=10 then 费用 else 0 end) as 十月份,
sum(case when 费用月份=11 then 费用 else 0 end) as 十一月份,
sum(case when 费用月份=12 then 费用 else 0 end) as 十二月份
from
TableName
group by
费用名称
Declare @S Varchar(1000)
Set @S=''
Select @S=@S+ ',['+费用月份+'月份]= SUM(Case 费用月份 When '''+费用月份+''' Then 费用 Else 0 End) ' From 表 Order By 费用月份
Set @S='Select 费用名称'+@S+' From 表 Group By 费用名称 Order By 费用名称'
EXEC (@S)
GO2:
Select
费用名称,
SUM(Case 费用月份 When 1 Then 费用 Else 0 End) As [1月份],
SUM(Case 费用月份 When 2 Then 费用 Else 0 End) As [2月份],
SUM(Case 费用月份 When 3 Then 费用 Else 0 End) As [3月份],
SUM(Case 费用月份 When 4 Then 费用 Else 0 End) As [4月份],
SUM(Case 费用月份 When 5 Then 费用 Else 0 End) As [5月份],
SUM(Case 费用月份 When 6 Then 费用 Else 0 End) As [6月份],
SUM(Case 费用月份 When 7 Then 费用 Else 0 End) As [7月份],
SUM(Case 费用月份 When 8 Then 费用 Else 0 End) As [8月份],
SUM(Case 费用月份 When 9 Then 费用 Else 0 End) As [9月份],
SUM(Case 费用月份 When 10 Then 费用 Else 0 End) As [10月份],
SUM(Case 费用月份 When 11 Then 费用 Else 0 End) As [11月份],
SUM(Case 费用月份 When 12 Then 费用 Else 0 End) As [12月份]
From 表
Group By 费用名称建议:
应该用2效率好一点