select eo.NameOfInstitution,s.LastSurname,s.FirstName, car.Description as CourseAttemptResultType, ct.CourseEducationOrganizationId, sy.SchoolYearDescription
, dt.CodeValue as Term, ct.CourseCode, c.CourseTitle, ct.AttemptedCredits, acrt.Description as AttemptedCreditType, ct.AttemptedCreditConversion
, ct.EarnedCredits, ecrt.Description as EarnedCreditType, ct.EarnedCreditConversion, dg.CodeValue as GradeTaken, mct.Description as MethodCreditEarned
, ct.FinalLetterGradeEarned, ct.FinalNumericGradeEarned, crct.Description as CourseRepeatCode, ct.CourseTitle as CourseTitleOther, ct.AlternativeCourseCode, ct.AlternativeCourseTitle
, das.CodeValue as SubjectCode,das.Description as Subject
from edfi.CourseTranscript ct
left join edfi.Student s on s.StudentUSI=ct.StudentUSI
left join edfi.EducationOrganization eo on eo.EducationOrganizationId=ct.EducationOrganizationId
left join edfi.Descriptor car on car.DescriptorId=ct.CourseAttemptResultDescriptorId
left join edfi.SchoolYearType sy on sy.SchoolYear=ct.SchoolYear
left join edfi.Course c on c.CourseCode=ct.CourseCode and c.EducationOrganizationId=ct.EducationOrganizationId 
left join edfi.Descriptor dt on dt.DescriptorId=ct.TermDescriptorId
left join edfi.Descriptor acrt on acrt.DescriptorId=ct.AttemptedCreditTypeDescriptorId
left join edfi.Descriptor ecrt on ecrt.DescriptorId=ct.EarnedCreditTypeDescriptorId
left join edfi.Descriptor dg on dg.DescriptorId=ct.WhenTakenGradeLevelDescriptorId
left join edfi.Descriptor mct on mct.DescriptorId=ct.MethodCreditEarnedDescriptorId
left join edfi.Descriptor crct on crct.DescriptorId=ct.CourseRepeatCodeDescriptorId
left join edfi.Descriptor das on das.DescriptorId=c.AcademicSubjectDescriptorId
order by eo.NameOfInstitution,s.LastSurname,s.FirstName,ct.SchoolYear,ct.TermDescriptorId, coalesce(c.CourseTitle,ct.CourseTitle)