select year,
quarter1=max(case when quarter=1 then amount else 0 end),
quarter2=max(case when quarter=2 then amount else 0 end),
quarter3=max(case when quarter=3 then amount else 0 end),
quarter4=max(case when quarter=4 then amount else 0 end)
from 表
group by year
quarter1=max(case when quarter=1 then amount else 0 end),
quarter2=max(case when quarter=2 then amount else 0 end),
quarter3=max(case when quarter=3 then amount else 0 end),
quarter4=max(case when quarter=4 then amount else 0 end)
from 表
group by year
,max(case when [quarter]=1 then amount else o end) as 'quarter1'
,max(case when [quarter]=2 then amount else o end) as 'quarter2'
,max(case when [quarter]=3 then amount else o end) as 'quarter3'
,max(case when [quarter]=4 then amount else o end) as 'quarter4'
from 表
group by [year]
quarter1=sum(case when quarter=1 then amount else 0 end),
quarter2=sum(case when quarter=2 then amount else 0 end),
quarter3=sum(case when quarter=3 then amount else 0 end),
quarter4=sum(case when quarter=4 then amount else 0 end)
from 表
group by year
declare @t table (year varchar(10),quarter int, amount int)
insert into @t select '2001',1,100
union all select '2001',2,100
union all select '2001',3,100
union all select '2001',4,100
union all select '2002',1,100
union all select '2002',2,100
union all select '2002',3,100
union all select '2002',4,100
union all select '2003',1,100
union all select '2003',2,100
union all select '2003',3,100
--查询
select year,
quarter1=max(case when quarter=1 then amount else 0 end),
quarter2=max(case when quarter=2 then amount else 0 end),
quarter3=max(case when quarter=3 then amount else 0 end),
quarter4=max(case when quarter=4 then amount else 0 end)
from @t
group by year
--结果
year quarter1 quarter2 quarter3 quarter4
---------- ----------- ----------- ----------- -----------
2001 100 100 100 100
2002 100 100 100 100
2003 100 100 100 0(所影响的行数为 3 行)
,max(case when [quarter]=1 then amount else o end) as quarter1
,max(case when [quarter]=2 then amount else o end) as quarter2
,max(case when [quarter]=3 then amount else o end) as quarter3
,max(case when [quarter]=4 then amount else o end) as quarter4
from 表
group by [year]
declare @s nvarchar(4000)
set @s=''select @s=@s+','+'max(case a.quarter when '''+a.quarter+''' then b.Amount else 0 end)[Quarter'+a.Quarter+']'
from (select distinct quarter from tb)aselect @s='select a.[year] '+@s+' from tb as a
inner join tb as b on a.Year=b.Year and a.Quarter=b.Quarter
group by a.Year'
exec (@s)
--Result
year Quarter1 Quarter2 Quarter3 Quarter4
---------- ----------- ----------- ----------- -----------
2001 100 100 100 100
2002 100 100 100 100
2003 100 100 100 0
--测试
declare @t table (year varchar(10),quarter int, amount int)
insert into @t select '2001',1,100
union all select '2001',2,100
union all select '2001',3,100
union all select '2001',4,100
union all select '2002',1,100
union all select '2002',2,100
union all select '2002',3,100
union all select '2002',4,100
union all select '2003',1,100
union all select '2003',2,100
union all select '2003',3,100--Sql Commanddeclare @s nvarchar(4000)
set @s=''select @s=@s+','+'max(case a.quarter when '+cast(a.quarter as nvarchar(50))+' then b.Amount else 0 end)[Quarter'+cast(a.quarter as nvarchar(50))+']'
from (select distinct quarter from tb)aselect @s='select a.[year] '+@s+' from tb as a
inner join tb as b on a.Year=b.Year and a.Quarter=b.Quarter
group by a.Year'
exec (@s)--result
year Quarter1 Quarter2 Quarter3 Quarter4
---------- --------------------- --------------------- --------------------- ---------------------
2001 100.0000 100.0000 100.0000 100.0000
2002 100.0000 100.0000 100.0000 100.0000
2003 100.0000 100.0000 100.0000 .0000
set @s=''select @s=@s+','+'max(case a.quarter when '+cast(a.quarter as nvarchar(50))+' then b.Amount else 0 end)[Quarter'+cast(a.quarter as nvarchar(50))+']'
from (select distinct quarter from tb)aselect @s='select a.[year] '+@s+' from tb as a
inner join tb as b on a.Year=b.Year and a.Quarter=b.Quarter
group by a.Year'
exec (@s)
,max(case when [quarter]=1 then amount else o end) as 'quarter1'
,max(case when [quarter]=2 then amount else o end) as 'quarter2'
,max(case when [quarter]=3 then amount else o end) as 'quarter3'
,max(case when [quarter]=4 then amount else o end) as 'quarter4'
from 表
group by [year]
,max(case when [quarter]=1 then amount else o end) as quarter1
,max(case when [quarter]=2 then amount else o end) as quarter2
,max(case when [quarter]=3 then amount else o end) as quarter3
,max(case when [quarter]=4 then amount else o end) as quarter4
from 表
group by [year]