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
