Report of Grades for single rubric in single course (T-SQL) - find/replace <rubric_pk1> to use

grade_by_rubric_report.sql

--step 3 user recieves report for selected rubric:
--    rubric_pks for a course can be gotten via:
--    select r.pk1 as rubric_pk1, r.title 
--      from rubric r where r.course_pk1 = [course_pk1]
--    sample output:
--    rubric_pk1   title
--    2515	       'Individual Assignment'

-- NOTE THIS QUERY BRANCHES BASED ON WHETHER THE RUBRIC IS 
-- ASSOCIATED WITH AN INDIVIDUAL OR GROUP ASSIGNMENT AND THE
-- COLUMN NAMES ARE NOT IDENTICAL

use bb_bb60;

declare @RUBRIC_PK1 int;
set @RUBRIC_PK1 = <rubric_pk1>

-- check to see if rubric is linked to group attempts
IF  (select count(group_attempt_pk1)
	from rubric_link rl inner join evaluation_entity ee on rl.eval_entity_pk1 = ee.pk1
	where rl.rubric_pk1 = @RUBRIC_PK1) = 0
-- if it's an indiviual assignment
BEGIN
	-- pks are commented out in the event someone wants them
	-- you can always just comment out everything from a specific table and use tbl.*
	-- i.e. r.* or rl.* in the below query
	select 
		r.title as 'rubric title',
		-- rl.pk1, rl.rubric_pk1, 
		u.USER_ID as 'user name', u.LASTNAME as 'last name', u.FIRSTNAME as 'first name',
		-- rl.eval_entity_pk1, re.pk1 as re_pk, 
		rr.header as 'row name', 
		rcol.header,  rcol.position + 1 as 'row score', 
		round(rce.selected_percent, 4) as 'row percentage',
		rce.feedback 'row feedback', 
		at.score as 'overall score', 
		at.INSTRUCTOR_COMMENTS as 'overall feedback'
	from rubric_link rl
		inner join rubric r on rl.rubric_pk1 = r.pk1
		inner join evaluation_entity ee on rl.eval_entity_pk1 = ee.pk1
		inner join ATTEMPT at on ee.attempt_pk1 = at.PK1
		inner join GRADEBOOK_GRADE gg on at.GRADEBOOK_GRADE_PK1 = gg.PK1
		inner join COURSE_USERS cu on gg.COURSE_USERS_PK1 = cu.PK1
		inner join USERS u on cu.USERS_PK1 = u.PK1
		inner join rubric_eval re on rl.pk1 = re.rubric_link_pk1 
		inner join rubric_cell_eval rce on re.pk1 = rce.rubric_eval_pk1
		inner join rubric_cell rc on rce.rubric_cell_pk1 = rc.pk1
		inner join rubric_column rcol on rc.rubric_column_pk1 = rcol.pk1
		inner join rubric_row rr on rce.rubric_row_pk1 = rr.pk1
	where rl.rubric_pk1 = @RUBRIC_PK1 and (ee.attempt_pk1 is not null);
END
-- if the rubrik is tied to a group assignment
ELSE BEGIN
Select  
	r.title as 'rubric title',
	ga.group_name as 'group title', 
	rr.header as 'row name',  
	rcol.header,  rcol.position + 1 as 'row score', 
	round(rce.selected_percent, 4) as 'row percentage',
	rce.feedback as 'row feedback', 
	ga.instructor_comments as 'overall feedback',
	ga.student_comments
  from rubric_link rl
	inner join rubric r on rl.rubric_pk1 = r.pk1
	inner join evaluation_entity ee on rl.eval_entity_pk1 = ee.pk1
	inner join group_attempt ga on ee.group_attempt_pk1 = ga.PK1
	inner join rubric_eval re on rl.pk1 = re.rubric_link_pk1 
	inner join rubric_cell_eval rce on re.pk1 = rce.rubric_eval_pk1
	inner join rubric_cell rc on rce.rubric_cell_pk1 = rc.pk1
	inner join rubric_column rcol on rc.rubric_column_pk1 = rcol.pk1
    inner join rubric_row rr on rce.rubric_row_pk1 = rr.pk1
	where r.pk1 = @RUBRIC_PK1
	order by ga.group_name
END

Sample result is here (pink columns are repeating data) - I copied and pasted into spreadsheets from sql studio results:

https://docs.google.com/spreadsheets/d/19FXKdIOEgEUked2pkP0yRWw7mjR4qPQ5RGp5x9Q8d0s/edit?usp=sharing

I didn't add a demo report for the group project - it's the same except username, first name, last name are replaced by a single column "group name"