按年月统计并行列转换(ms sqlserver2005)

概述按年月统计并行列转换(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] PRIMAR

按年月统计并行列转换(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

 

有更好的方法欢迎贴上来,学习下

总结

以上是内存溢出为你收集整理的按年月统计并行列转换(ms sqlserver2005)全部内容，希望文章能够帮你解决按年月统计并行列转换(ms sqlserver2005)所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。