SELECT examinee_name as '考官评分', MAX( CASE examiner_name WHEN '考官1' THEN SCORE ELSE 0 END ) as '考官1' , MAX( CASE examiner_name WHEN '考官2' THEN SCORE ELSE 0 END ) as '考官2' , MAX( CASE examiner_name WHEN '考官3' THEN SCORE ELSE 0 END ) as '考官3', MAX( CASE examiner_name WHEN '考官4' THEN SCORE ELSE 0 END ) as '考官4', MAX( CASE examiner_name WHEN '考官5' THEN SCORE ELSE 0 END ) as '考官5', MAX( CASE examiner_name WHEN '考官6' THEN SCORE ELSE 0 END ) as '考官6', MAX( CASE examiner_name WHEN '考官7' THEN SCORE ELSE 0 END ) as '考官7', t_kgpj_examinee.interview_score as '面试评分' FROM t_kgpj_grade ,t_kgpj_examinee where t_kgpj_examinee.fname=t_kgpj_grade.examinee_name GROUP BY examinee_name,t_kgpj_examinee.interview_score; 

效果图

其他需求

SELECT examinee_id, examinee_name, 考官1_402881135ff22b9d015ff230574a0000, 考官2_402881135ff22b9d015ff23057700001, 考官3_402881135ff22b9d015ff23057720002, 考官4_402881135ff22b9d015ff23057750003, 考官5_402881135ff22b9d015ff23057770004, 考官6_402881135ff22b9d015ff230577a0005, 考官7_402881135ff22b9d015ff230577c0006, interview_score FROM (SELECT examinee_id, examinee_name, score, examiner_name + '_' + examiner_id AS nid, t_kgpj_examinee.interview_score FROM t_kgpj_grade, dbo.t_kgpj_examinee WHERE t_kgpj_grade.examinee_name = t_kgpj_examinee.fname) AS g PIVOT ( AVG(g.score) FOR g.nid IN ( 考官1_402881135ff22b9d015ff230574a0000, 考官2_402881135ff22b9d015ff23057700001, 考官3_402881135ff22b9d015ff23057720002, 考官4_402881135ff22b9d015ff23057750003, 考官5_402881135ff22b9d015ff23057770004, 考官6_402881135ff22b9d015ff230577a0005, 考官7_402881135ff22b9d015ff230577c0006 ) ) T 

阅读更多

更多精彩内容