按年月统计并行列转换代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】按年月统计并行列转换

作者:Dezai.CN / 发布于2012/3/9/ 624

<div>
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,2010,2
union all
select 200,2010,2
union all
select 200,2010,2
union all
select 180,2010,5
union all
select 100,2010,7
union all
select 150,2010,8
union all
select 150,2010,8
union all
select 150,2010,8
union all
select 108,2010,10
union all
select 100,2010,11
union all
select 108,2010,12
union all 
select 200,2009,2
union all
select 180,2009,5
union all
select 100,2009,7
union all
select 150,2009,8
union all
select 150,2009,8
union all
select 150,2009,8
union all
select 108,2009,10
union all
select 100,2009,11
union all
select 108,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 ),
[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
</div>

评论列表

本站所提供的代码，版权归原作者所有，若有侵犯作者版权，请与我们联系，我们将立即删除或修改。谢谢!
本站所有代码发布及提供者。

试试其它关键字

　按年月统计并行列转换　

同语言下

可能有用的

Dezai.CN贡献的其它代码(4037)