Here is i think what you are looking for..

select distinct

a.cStudentID,

a.School_Year,

a.CourseID,

case when b.termcode is not null then b.termcode else a.termcode end as 'termcode' ,

case when b.termcode is not null then b.mark else a.mark end as 'mark'

from

#temp a left outer join

(

select

b.*

from #temp a inner join #temp b

on a.cStudentID = b.cStudentID and a.School_Year = b.School_Year and a.CourseID = b.CourseID

where a.TermCode = 'Q1' and b.termcode = 'FG1'

)

b on a.cStudentID = b.cStudentID and a.CourseID = b.CourseID

For the articles and blogs about SQL please visit... SQL Concepts, scripts and much more