SQL - Grab Detail Rows as Columns in Join.

Question

This is my query thus far:

select C.ACCOUNTNO,C.CONTACT,KEY1,KEY4 from contact1 C
left join CONTSUPP CS on C.accountno=CS.accountno
where 
C.KEY1!='00PRSP' 
AND (C.U_KEY2='2009 FALL' 
    OR C.U_KEY2='2010 SPRING' 
    OR C.U_KEY2='2010 J TERM' 
    OR C.U_KEY2='2010 SUMMER')

Now, I have another table (CONTSUPP) which contains multiple detail records for each record in CONTACT1. For example - High School GPA, SAT, ACT, etc. I need to pull these values into the same row as my contact1 results - but determine the column header based on information within the column. For example, I know I could do this:

select C.ACCOUNTNO,C.CONTACT,KEY1,KEY4,STATE from contact1 C
left join CONTSUPP CS on C.accountno=CS.accountno
where 
C.KEY1!='00PRSP' 
AND (C.U_KEY2='2009 FALL' 
    OR C.U_KEY2='2010 SPRING' 
    OR C.U_KEY2='2010 J TERM' 
    OR C.U_KEY2='2010 SUMMER')

Don't ask why (please) - but state holds the grade values. The problem is that this gives me grades without telling what type of grades, so I need to do something like this (pseudo):

select C.ACCOUNTNO,C.CONTACT,C.KEY1,C.KEY4,
  STATE as GPA when CS.CONTACT='High School'
  STATE as SAT when CS.CONTACT='Test/SAT'
  ..
from contact1 C
left join CONTSUPP CS on C.accountno=CS.accountno
where 
C.KEY1!='00PRSP' 
AND (C.U_KEY2='2009 FALL' 
    OR C.U_KEY2='2010 SPRING' 
    OR C.U_KEY2='2010 J TERM' 
    OR C.U_KEY2='2010 SUMMER')

Help?

Answer 1

select 
    C.ACCOUNTNO,
    C.CONTACT,
    C.KEY1,
    C.KEY4,  
    HichschoolCS.State as HighSchool,  
    TestSatCS.state as Test


from 
    contact1 C
    left join CONTSUPP HichschoolCS on C.accountno=HichschoolCS.accountno 
        and HichschoolCS.contact = 'High School'
    left join CONTSUPP TestSatCS on C.accountno=TestSatCS.accountno 
        and TestSatCS.contact = 'Test/SAT'
where 
    C.KEY1!='00PRSP' 
    AND (C.U_KEY2='2009 FALL' 
    OR C.U_KEY2='2010 SPRING' 
    OR C.U_KEY2='2010 J TERM' 
    OR C.U_KEY2='2010 SUMMER')

Update: Added example of only using the highest SAT score

select 
    C.ACCOUNTNO,
    C.CONTACT,
    C.KEY1,
    C.KEY4,  
    HichschoolCS.State as HighSchool,  
    TestSatCS.state as Test


from 
    contact1 C
    left join CONTSUPP HichschoolCS on C.accountno=HichschoolCS.accountno 
        and HichschoolCS.contact = 'High School'
    left join (SELECT MAX(state) state, 
        accountno
        FROM
            CONTSUPP TestSatCS 
        WHERE 
            contact = 'Test/SAT'
        GROUP
            accountno) TestSatCS
    on C.accountno=TestSatCS.accountno 

where 
    C.KEY1!='00PRSP' 
    AND (C.U_KEY2='2009 FALL' 
    OR C.U_KEY2='2010 SPRING' 
    OR C.U_KEY2='2010 J TERM' 
    OR C.U_KEY2='2010 SUMMER')