SELECT
   st.suniq,
   sd.firstname,
   sd.lastname,
   tD.testuniq,
   - CASE WHEN (td.subtestc)='LI' THEN  st.testscore END AS [CELDT Listening],
   --CASE WHEN (td.subtestc)='SP' THEN  st.testscore END AS [CELDT Speaking],
   --CASE WHEN (td.subtestc)='RD' THEN  st.testscore END AS [CELDT Reading],
   --CASE WHEN (td.subtestc)='WR' THEN  st.testscore END AS [CELDT Writing],
   --CASE WHEN (td.subtestc)='TO' THEN  st.testscore END AS [CELDT Overall],
   td.testc,
   td.subtestc,
   zst.descript,
   st.takendt,
   st.grdlvl,
   st.testscore
   FROM dbo.stutests st
   JOIN dbo.testdef td ON td.testuniq=st.testuniq
   JOIN studemo sd ON sd.suniq=st.suniq
   JOIN zsubtest zst ON zst.subtestc=td.subtestc
   WHERE tscrtypc='S'
   AND td.testc='CELDT'
   AND takendt LIKE '%2013%';

suniq    firstname   lastname    subtestc    descript      grdlvl    testscore

   254585   Brenda      Cazares         LI      CELDT Listening 8         534
   254585   Brenda      Cazares         SP      CELDT Speaking  8         612
   254585   Brenda      Cazares         RD      CELDT Reading   8         571
   254585   Brenda      Cazares         WR      CELDT Writing   8         680
   254585   Brenda      Cazares         WR      CELDT overall   8         710

suniq  firstname lastname  Listening  Speaking  Reading Writing  Overall  grdlve
  254585 Brenda     Cazares     534      612       571      680      710     8