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