笔记253 行列转换使用sum函数统计好然后根据服务人员姓名来排序 2013-4-29

 1 --行列转换 使用sum函数统计好然后根据服务人员姓名来排序 2013-4-29
 2 USE [tempdb]
 3 GO
 4 CREATE TABLE #service(IT_Service_ID INT IDENTITY(1,1) NOT NULL,Category NCHAR(10),DESCRIPTION NTEXT,Solution NTEXT,Name_Contact NCHAR(10)
 5 ,Department_Info NCHAR(30),E_Mail_Contact NCHAR(40),Tel NCHAR(20),Mobile NCHAR(20),Name_Service NCHAR(10),
 6 Time_Service DATETIME,Time_Creatd DATETIME,Time_Completed DATETIME,Time_Consuming FLOAT,STATUS NCHAR(10),Evaluate NCHAR(10))
 7 GO
 8 
 9 INSERT INTO [#service]
10         (
11           [Category] ,
12           [DESCRIPTION] ,
13           [Solution] ,
14           [Name_Contact] ,
15           [Department_Info] ,
16           [E_Mail_Contact] ,
17           [Tel] ,
18           [Mobile] ,
19           [Name_Service] ,
20           [Time_Service] ,
21           [Time_Creatd] ,
22           [Time_Completed] ,
23           [Time_Consuming] ,
24           [STATUS] ,
25           [Evaluate]
26         )      
27 SELECT 'ERP',NULL,NULL,'','策划部','','','','李明','','','',1.7,'','4星' UNION ALL
28 SELECT 'PLM',NULL,NULL,'','销售部','','','','方欣','','','',1.8,'','5星'
29 
30    
31 SELECT * FROM [#service]
32 
33 SELECT
34   Name_Service  AS  [服务人员姓名],
35   SUM( CASE WHEN Category = '桌面端' THEN 1 ELSE 0 END ) AS [桌面端],
36   SUM( CASE WHEN Category = 'ERP' THEN 1 ELSE 0 END ) AS ERP,
37   SUM( CASE WHEN Category = 'PLM' THEN 1 ELSE 0 END ) AS PLM,
38   SUM( CASE WHEN Category = '其他' THEN 1 ELSE 0 END ) AS [其他],
39   SUM( CASE WHEN Evaluate = '5星' THEN 1 ELSE 0 END ) AS [5星],
40   SUM( CASE WHEN Evaluate = '4星' THEN 1 ELSE 0 END ) AS [4星],
41   SUM( CASE WHEN Evaluate = '3星' THEN 1 ELSE 0 END ) AS [3星],
42   SUM( CASE WHEN Evaluate = '2星' THEN 1 ELSE 0 END ) AS [2星],
43   SUM( CASE WHEN Evaluate = '1星' THEN 1 ELSE 0 END ) AS [1星]
44 FROM
45   #service
46 GROUP BY
47   Name_Service