The code creates a dynamic column for ordinal ranking. It...

November 21, 2023 at 09:51 AM

-- Create Dynamic Column for Ordinal Ranking
						SET @AVG_RankJudges = ( 
								SELECT GROUP_CONCAT(
									REPLACE(Category_Description, ' ', '_'), '_',Candidate_ID,', 
									FORMAT(AVG(Row_',REPLACE(Category_Description, ' ', '_'),'_',Candidate_ID,') 
										OVER (PARTITION BY ',REPLACE(Category_Description, ' ', '_'), '_',Candidate_ID,' ), 2) 
											AS category_',REPLACE(Category_Description, ' ', '_'),'_Rank_', Candidate_ID
								ORDER BY Candidate_ID SEPARATOR ',  ') AS Ranks
								
									FROM (
											SELECT DISTINCT scores.Candidate_ID, Category_Description
											FROM scores
											INNER JOIN candidate ON scores.Candidate_ID = candidate.Candidate_ID
											INNER JOIN criteria ON scores.Criteria_ID = criteria.Criteria_ID
											INNER JOIN category ON criteria.Category_ID = category.Category_ID
											WHERE candidate.Gender = @s_key
									) AS distinct_judges
						);

The code creates a dynamic column for ordinal ranking. It calculates the average ranking for each candidate in each category and formats the result. The result is stored in a variable called @AVG_RankJudges.

Generate your own explanations

Download our vscode extension

Read other generated explanations

Built by @thebuilderjr