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

最新推荐文章于 2024-03-21 17:26:19 发布

原创最新推荐文章于 2024-03-21 17:26:19 发布 · 1.5k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sqlserver #2010 #statistics #null #insert #table

MS SQL 专栏收录该内容

52 篇文章

订阅专栏

本文介绍如何使用MSSQL Server 2005进行按年份月份的数据统计及行列转换，包括创建表、初始化数据及两种实现方法：一种适用于SQL 2005，另一种适用于SQL 2000。

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

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