SQL - Row_Number() OVER (ORDER BY)

row_number.sql

SELECT ApplicantID, Email, FName, LName, Application_Status,
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = @programType)) AS Conflicts
WHERE RowNum = 1))) AS Conflict01,
(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames01
WHERE RowNum = 1) as Rev1Name,
(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs01
WHERE RowNum = 1))) AS Priority01,
------------------------------------------------------------------------------------------------
(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts02
WHERE RowNum = 2))) AS Conflict02,

(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames02
WHERE RowNum = 2) as Rev2Name,

(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs02
WHERE RowNum = 2))) AS Priority02,
------------------------------------------------------------------------------------------------

(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts03
WHERE RowNum = 3))) AS Conflict03,

(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames03
WHERE RowNum = 3) as Rev3Name,

(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs03
WHERE RowNum = 3))) AS Priority03,
------------------------------------------------------------------------------------------------

(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts04
WHERE RowNum = 4))) AS Conflict04,

(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames04
WHERE RowNum = 4) as Rev4Name,

(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs04
WHERE RowNum = 4))) AS Priority04,
------------------------------------------------------------------------------------------------

(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts05
WHERE RowNum = 5))) AS Conflict05,

(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames05
WHERE RowNum = 5) as Rev5Name,

(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs05
WHERE RowNum = 5))) AS Priority05,
------------------------------------------------------------------------------------------------

(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts06
WHERE RowNum = 6))) AS Conflict06,

(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames06
WHERE RowNum = 6) as Rev6Name,

(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs06
WHERE RowNum = 6))) AS Priority06,
------------------------------------------------------------------------------------------------

(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts07
WHERE RowNum = 7))) AS Conflict07,

(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames07
WHERE RowNum = 7) as Rev7Name,

(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs07
WHERE RowNum = 7))) AS Priority07,
------------------------------------------------------------------------------------------------

(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts08
WHERE RowNum = 8))) AS Conflict08,

(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames08
WHERE RowNum = 8) as Rev8Name,

(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs08
WHERE RowNum = 8))) AS Priority08,
------------------------------------------------------------------------------------------------

(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts09
WHERE RowNum = 9))) AS Conflict09,

(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames09
WHERE RowNum = 9) as Rev9Name,

(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs09
WHERE RowNum = 9))) AS Priority09,
------------------------------------------------------------------------------------------------

(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts10
WHERE RowNum = 10))) AS Conflict10,

(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames10
WHERE RowNum = 10) as Rev10Name,

(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs10
WHERE RowNum = 10))) AS Priority10,
------------------------------------------------------------------------------------------------

(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts11
WHERE RowNum = 11))) AS Conflict11,

(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames11
WHERE RowNum = 11) as Rev11Name,

(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs11
WHERE RowNum = 11))) AS Priority11,
------------------------------------------------------------------------------------------------

(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts12
WHERE RowNum = 12))) AS Conflict12,

(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames12
WHERE RowNum = 12) as Rev12Name,

(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs12
WHERE RowNum = 12))) AS Priority12,
------------------------------------------------------------------------------------------------

(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts13
WHERE RowNum = 13))) AS Conflict13,

(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames13
WHERE RowNum = 13) as Rev13Name,

(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs13
WHERE RowNum = 13))) AS Priority13,
------------------------------------------------------------------------------------------------

(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts14
WHERE RowNum = 14))) AS Conflict14,

(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames14
WHERE RowNum = 14) as Rev14Name,

(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs14
WHERE RowNum = 14))) AS Priority14,
------------------------------------------------------------------------------------------------

(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts15
WHERE RowNum = 15))) AS Conflict15,

(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames15
WHERE RowNum = 15) as Rev15Name,

(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs15
WHERE RowNum = 15))) AS Priority15,
------------------------------------------------------------------------------------------------

(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts16
WHERE RowNum = 16))) AS Conflict16,

(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames16
WHERE RowNum = 16) as Rev16Name,

(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs16
WHERE RowNum = 16))) AS Priority16,
------------------------------------------------------------------------------------------------

(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts17
WHERE RowNum = 17))) AS Conflict17,

(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames17
WHERE RowNum = 17) as Rev17Name,

(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs17
WHERE RowNum = 17))) AS Priority17,
------------------------------------------------------------------------------------------------

(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts18
WHERE RowNum = 18))) AS Conflict18,

(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames18
WHERE RowNum = 18) as Rev18Name,

(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs18
WHERE RowNum = 18))) AS Priority18,
------------------------------------------------------------------------------------------------

(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts19
WHERE RowNum = 19))) AS Conflict19,

(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames19
WHERE RowNum = 19) as Rev19Name,

(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs19
WHERE RowNum = 19))) AS Priority19,
------------------------------------------------------------------------------------------------

(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts20
WHERE RowNum = 20))) AS Conflict20,

(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames20
WHERE RowNum = 20) as Rev20Name,

(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs20
WHERE RowNum = 20))) AS Priority20,
------------------------------------------------------------------------------------------------

(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts21
WHERE RowNum = 21))) AS Conflict21,

(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames21
WHERE RowNum = 21) as Rev21Name,

(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs21
WHERE RowNum = 21))) AS Priority21,
------------------------------------------------------------------------------------------------

(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts22
WHERE RowNum = 22))) AS Conflict22,

(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames22
WHERE RowNum = 22) as Rev22Name,

(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs22
WHERE RowNum = 22))) AS Priority22,
------------------------------------------------------------------------------------------------

(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts23
WHERE RowNum = 23))) AS Conflict23,

(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames23
WHERE RowNum = 23) as Rev23Name,

(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs23
WHERE RowNum = 23))) AS Priority23,
------------------------------------------------------------------------------------------------

(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts24
WHERE RowNum = 24))) AS Conflict24,

(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames24
WHERE RowNum = 24) as Rev24Name,

(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs24
WHERE RowNum = 24))) AS Priority24,
------------------------------------------------------------------------------------------------

(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts25
WHERE RowNum = 25))) AS Conflict25,

(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames25
WHERE RowNum = 25) as Rev25Name,

(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs25
WHERE RowNum = 25))) AS Priority25,
------------------------------------------------------------------------------------------------

(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts26
WHERE RowNum = 26))) AS Conflict26,

(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames26
WHERE RowNum = 26) as Rev26Name,

(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs26
WHERE RowNum = 26))) AS Priority26,
------------------------------------------------------------------------------------------------

(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts27
WHERE RowNum = 27))) AS Conflict27,

(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames27
WHERE RowNum = 27) as Rev27Name,

(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs27
WHERE RowNum = 27))) AS Priority27,
------------------------------------------------------------------------------------------------

(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts28
WHERE RowNum = 28))) AS Conflict28,

(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames28
WHERE RowNum = 28) as Rev28Name,

(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs28
WHERE RowNum = 28))) AS Priority28,
------------------------------------------------------------------------------------------------

(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts29
WHERE RowNum = 29))) AS Conflict29,

(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames29
WHERE RowNum = 29) as Rev29Name,

(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs29
WHERE RowNum = 29))) AS Priority29,
------------------------------------------------------------------------------------------------

(SELECT CASE WHEN Conflicts.conflictId IS NULL THEN 0 ELSE 1 END AS HasConflict
FROM Conflicts
WHERE (applicantId = Applicant.ApplicantID) AND (reviewerId =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS Conflicts30
WHERE RowNum = 30))) AS Conflict30,

(SELECT ReviewerName FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevNames30
WHERE RowNum = 30) as Rev30Name,

(SELECT PriorityLevel
FROM MR_Applicant
WHERE (ApplicantID = Applicant.ApplicantID) AND (ReviewerID =
(SELECT ReviewerID FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ReviewerName ASC) AS RowNum, *
FROM vwAssignedReviewers
WHERE (Program_Name = 'mtpci')) AS RevIDs30
WHERE RowNum = 30))) AS Priority30
FROM Applicant
WHERE (Program_Type = 'mtpci')

Most complicated SQL I ever wrote.