How to combine columns, group them then get a total count?

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!

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