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