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

Question

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

Answer 1

In SQL Server, it becomes a small matter for a CROSS APPLY

Example

Select A.[candidate_id]  --<< Included because it seemed odd not to.
      ,B.*
 From YourTable A
 Cross Apply ( values ([interview_1],case when [result_1]='Pass' then 1 else 0 end,case when [result_1]<>'Pass' then 1 else 0 end)
                     ,([interview_2],case when [result_2]='Pass' then 1 else 0 end,case when [result_2]<>'Pass' then 1 else 0 end)
             ) B(Interview_name,pass_count,reject_count)

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

Answer 2

You could pull the dataset twice and union together, I'm assuming it's not an unwieldy set.

Something like -

WITH combined_set AS(
SELECT
 candidate_id,
 interviewer_1 as interviewer,
 result_1 as result
from candidate_table

UNION

SELECT
 candidate_id,
 interviewer_2 as interviewer,
 result_2 as result
from candidate_table)

SELECT
 interviewer,
 count(case when result = 'Pass' then 1 end) as pass_count,
 count(case when result = 'Reject' then 1 end) as reject_count
FROM combined_set