|
按年月统计并行列转换(ms sqlserver2005)
1 创建表
?
CREATE TABLE [dbo].[Orders](
?[ID] [int] IDENTITY(1,1) NOT NULL,
?[Amount] [int] NULL,
?[Year] [int] NULL,
?[Month] [smallint] NULL,
?CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
?[ID] ASC
)WITH (PAD_INDEX? = OFF,STATISTICS_NORECOMPUTE? = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS? = ON,ALLOW_PAGE_LOCKS? = ON) ON [PRIMARY]
) ON [PRIMARY]
?
2 初始化数据
insert into [Orders]
select 100,2010,1
union all
select 200,2
union all
select 200,2
union all
select 180,5
union all
select 100,7
union all
select 150,8
union all
select 150,8
union all
select 108,10
union all
select 100,11
union all
select 108,12
union all
select 200,2009,12
select * from [Orders]
?
--------sql 2005------
SELECT *
FROM
( SELECT year,month,amount
FROM [Orders]) p
PIVOT
(SUM (amount)
FOR month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS amount
-------sql 2000-----
select year,
[1] = Sum ( case when month ='1' then amount else 0 end ),
[2] = Sum ( case when month ='2' then amount else 0 end ),
[3] = Sum ( case when month ='3' then amount else 0 end ),
[4] = Sum ( case when month ='4' then amount else 0 end ),
[5] = Sum ( case when month ='5' then amount else 0 end ),
[6] = Sum ( case when month ='6' then amount else 0 end ),
[7] = Sum ( case when month ='7' then amount else 0 end ),
[8] = Sum ( case when month ='8' then amount else 0 end ),
[9] = Sum ( case when month ='9' then amount else 0 end ),
[10] = Sum ( case when month ='10' then amount else 0 end ),
[12] = Sum ( case when month ='12' then amount else 0 end ),
[count] = Sum ( case when month <> '' then amount else 0 end )
from [Orders]
GROUP BY year
?
有更好的方法欢迎贴上来,学习下
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
