Get the pm_prof_code from the student_professional table

See more:

SQL-Server-2008R2

student_professional table as follows

stud_id pm_prof_code Certificate_no issue_authority  issue_Date
53248     COC	     COCTEST	     MMD             01 Jan 1920  
53248    GRADE	      MEOL	     MMD	     13 Mar 2015

First query as follows

select first.courseelg as Code,second.Certificate_No,second.Issue_Authority,second.Issue_Date from
       (select distinct b.courseelg,Course = case cr.cpm_pkg_id WHEN ''THEN cr.cmn_minor_code else cbm.cmn_minor_code end
        from course_registration cr, batch_course_registration bcr, co_batch_master cbm , tb_courseelg_settings b,student_professional a where
        bcr.cr_bill_no = cr.cr_bill_no and cbm.cbm_batch_id = bcr.bcr_batch_id and cr.cr_active = 'A' and
        cr.stud_id = '53248' and  cr.cmn_minor_Code = b.coursename and cr.cmn_minor_code = 'mfa') as first
        left join
        (select distinct b.coursename,a.pm_prof_code as Code,a.sp_cert_no as Certificate_No,
        a.sp_issu_authority as Issue_Authority, convert(char(14),a.sp_issu_dt,106) as Issue_Date from
        student_professional a,tb_courseelg_settings b
        where a.pm_prof_code= b.courseelg and a.stud_id = '53248' and b.coursename 'mfa' or (a.pm_prof_code = 'grade' and a.stud_id ='53248') or (a.pm_prof_code ='COC' and a.stud_id ='53248')) as second
        ON(second.coursename=first.Course) 

When i run the above query output as follows

code    Certificate_no  Issue_Authority       issue_Date
EFA	COCTEST	         MMD	              01 Jan 1920   
EFA	EFA/BTEST/7	 HIMT	              19 Sep 2010   
EFA	MEOL	         MMD	              13 Mar 2015   

From the above output i want COC and Grade in the Code Column from the student_professional table.

Correct output i want as follows

code    Certificate_no  Issue_Authority       issue_Date
COC     COCTEST	         MMD	              01 Jan 1920   
EFA	EFA/BTEST/7	 HIMT	              19 Sep 2010   
GRADE   MEOL	         MMD	              13 Mar 2015

from the above first query how can i change and get the output.

for that how can i do.please help me.