34,590
社区成员
发帖
与我相关
我的任务
分享
--sql server 2005下,我有以下的表:
if object_id('tb') is not null drop table tb
create table tb([type] char(1), [date] int check([date] between 1900 and 2050), [sales] int check([sales]>=0))
GO
insert into tb
select 'A', 2002, 200 union all
select 'B', 2003, 300 union all
select 'A', 2001, 400 union all
select 'A', 2001, 600 union all
select 'B', 2009, 700 union all
select 'C', 2010, 800 union all
select 'B', 2010, 800 union all
select 'C', 2003, 900
--想得到如下结果,即每一种[type]每一年的平均[sales]([date]和[type]固定,不需用动态执行)
/*
type 2001 2002 2003 2009 2010
----- ------ ------ ------ ------ ------
A 500 200 0 0 0
B 0 0 300 700 800
C 0 0 900 0 800
*/
--我写了两种方法
--方法1
select [type],sum([2001]) '2001',sum([2002]) '2002',sum([2003]) '2003',
sum([2009]) '2009', sum([2010]) '2010'from(
select [type],avg(case when date=2001 then [sales] else 0 end) '2001',
avg(case when date=2002 then [sales] else 0 end) '2002',
avg(case when date=2003 then [sales] else 0 end) '2003',
avg(case when date=2009 then [sales] else 0 end) '2009',
avg(case when date=2010 then [sales] else 0 end) '2010'
from tb group by [type],[date])t group by [type]
--方法2
select [type] 'type',isnull([2001],0) as '2001',isnull([2002],0) as '2002',
isnull([2003],0) as '2003',
isnull([2009],0) as '2009',isnull([2010],0) as '2010'
from tb pivot
(avg(sales) for [date] in([2001],[2002],[2003],[2009],[2010])) as pivt
--但是我觉得我的第一种方法太复杂了,使用select...case..有简单的方法不?
--这样写比较好吧
select [type],max(case when date=2001 then [sales] else 0 end) '2001',
max(case when date=2002 then [sales] else 0 end) '2002',
max(case when date=2003 then [sales] else 0 end) '2003',
max(case when date=2009 then [sales] else 0 end) '2009',
max(case when date=2010 then [sales] else 0 end) '2010'
from
(select [type],[date], avg([sales]) as [sales] from tb group by [type],[date]) a
group by [type]