怎么把这样一个表儿
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([year] int,[month] int,[amount] numeric(2,1))
insert [TB]
select 1991,1,1.1 union all
select 1991,2,1.2 union all
select 1991,3,1.3 union all
select 1991,4,1.4 union all
select 1992,1,2.1 union all
select 1992,2,2.2 union all
select 1992,3,2.3 union all
select 1992,4,2.4
select [year],
m1=max(case when [month]=1 then amount else 0 end),
m2=max(case when [month]=2 then amount else 0 end),
m3=max(case when [month]=3 then amount else 0 end),
m4=max(case when [month]=4 then amount else 0 end)
from tb
group by [year]
/*
year m1 m2 m3 m4
----------- ---- ---- ---- ----
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4(所影响的行数为 2 行)*/
drop table TB
Select @s=isnull(@s+',','')+'['+left([month],2)+'月]=max(case when [month]='+quotename([month],'''')+' then [amount] else 0 end)'
from TB group by [month]
exec('select [year],'+@s+' from tb group by [year]')/*
year 1月 2月 3月 4月
----------- ---- ---- ---- ----
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4*/
(select [year],[month],amount
from tb ) D pivot (max(amount) for [month] in ([1],[2],[3],[4]))
不知道对不对 没有2005
Select year, m1=Sum(Case When month=1 then amount else 0 end),
m2=Sum(Case When month=2 then amount else 0 end),
m3=Sum(Case When month=3 then amount else 0 end),
m4=Sum(Case When month=4 then amount else 0 end)
From #t2
Group by year