按年月统计并行列转换(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
按年月统计并行列转换(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 初始化数据
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
有更好的方法欢迎贴上来,学习下
查看更多关于按年月统计并行列转换(mssqlserver2005)的详细内容...