Below is a table that has candidate_id, two interviews they attended with the interviewer’s name, and results for each interview.
candidate_id | interview_1 | interview_2 | result_1 | result_2 |
---|---|---|---|---|
1 | Interviewer_A | Interviewer_B | Pass | Pass |
2 | Interviewer_C | Interviewer_D | Pass | Reject |
I need help to combine column interview_1 and interview_2 into one column, and count how many pass and reject each interviewer gave to the candidate, the result I expected to see as below:
interviewer_name | pass_count | reject_count |
---|---|---|
Interviewer_A | 1 | 0 |
Interviewer_B | 1 | 0 |
Interviewer_C | 1 | 0 |
Interviewer_D | 0 | 1 |
SQL or Python either would work for me! Much appreciated!
Advertisement
Answer
In SQL Server, it becomes a small matter for a CROSS APPLY
Example
x
Select [candidate_id]
,B.[Interview_name]
,pass_count = case when result='Pass' then 1 else 0 end
,reject_count = case when result='Pass' then 0 else 1 end
From YourTable A
Cross Apply ( values ([interview_1],[result_1])
,([interview_2],[result_2])
) B(Interview_name,result)
Results
candidate_id Interview_name pass_count reject_count
1 Interviewer_A 1 0
1 Interviewer_B 1 0
2 Interviewer_C 1 0
2 Interviewer_D 0 1