SQl2005行列转换 pivot/unpivot

一：建表

CREATE TABLE [dbo].[WLBugRecord](
 [BugID] [int] IDENTITY(1,1) NOT NULL,
 [title] [varchar](50) NULL,
 [Created] [datetime] NULL,
 [cateid] [int] NOT NULL CONSTRAINT [DF_WLBugRecord_cateid]  DEFAULT ((0)),
 CONSTRAINT [PK_BugRecord] PRIMARY KEY CLUSTERED
(
 [BugID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

 

二：插入测试数据：

INSERT INTO [dbo].[WLBugRecord]([title],[Created],[cateid]) VALUES  ('test1' ,'2010-05-24 18:20:50.333','1')
INSERT INTO [dbo].[WLBugRecord]([title],[Created],[cateid]) VALUES  ('test2' ,'2010-05-25 18:20:50.333','1')
INSERT INTO [dbo].[WLBugRecord]([title],[Created],[cateid]) VALUES  ('test3' ,'2010-05-26 18:20:50.333','2')
INSERT INTO [dbo].[WLBugRecord]([title],[Created],[cateid]) VALUES  ('test4' ,'2010-05-24 18:20:50.333','4')
INSERT INTO [dbo].[WLBugRecord]([title],[Created],[cateid]) VALUES  ('test5' ,'2010-05-25 18:20:50.333','3')
INSERT INTO [dbo].[WLBugRecord]([title],[Created],[cateid]) VALUES  ('test6' ,'2010-05-26 18:20:50.333','5')
INSERT INTO [dbo].[WLBugRecord]([title],[Created],[cateid]) VALUES  ('test7' ,'2010-05-24 18:20:50.333','6')
INSERT INTO [dbo].[WLBugRecord]([title],[Created],[cateid]) VALUES  ('test8' ,'2010-05-24 18:20:50.333','7')

三：

        select * from WLBugRecord

 

四：Pivot使用

 

select sum([1])  as '游戏系统',sum([2]) as '游戏操作',sum([3]) as '游戏场景',sum([4]) as '游戏物品',sum([5]) as '游戏任务',sum([6]) as '角色技能',sum([7]) as '其它',times from
(
SELECT  CONVERT(varchar(30),created,23)as times,[1] , [2], [3] , [4] , [5] , [6],[7]
FROM  WLBugRecord s
PIVOT (count(s.cateid) for s.cateid in([1],[2],[3],[4],[5],[6],[7]))AS pvt
) a
group by times

 执行时出现错误：'PIVOT' 附近有语法错误。您可能需要将当前数据库的兼容级别设置为更高的值，以启用此功能。有关存储过程 sp_dbcmptlevel 的信息，请参见帮助。

解决方法：EXEC   sp_dbcmptlevel  【数据库名字】,90

我想要的数据是按天分类汇总,把上面语句修改一下就可以：

select sum([1])  as '游戏系统',sum([2]) as '游戏操作',sum([3]) as '游戏场景',sum([4]) as '游戏物品',sum([5]) as '游戏任务',sum([6]) as '角色技能',sum([7]) as '其它',times from
(
SELECT  CONVERT(varchar(30),created,23)as times,[1] , [2], [3] , [4] , [5] , [6],[7]
FROM  WLBugRecord s
PIVOT (count(s.cateid) for s.cateid in([1],[2],[3],[4],[5],[6],[7]))AS pvt
) a
group by times

五：Unpivot使用

把上面的语句写成视图，用unpivot进行行列转换

select times,typename,num
from  【视图名称】 b
unpivot(num for typename in(游戏系统,游戏操作,游戏场景,游戏物品,游戏任务,角色技能,其它)) as unpvt
order by times

这里应该无需用视图就可以使用unpivot查出如上图的结果。有兴趣可研究。