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
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