TSQL Error in PIVOT statement

Question

Here is my script that is giving me error on the PIVOT statement. I am going nuts and not sure what is wrong there. Could someone please help?

SELECT p.id, p.ln, p.fn, p.sc, p.gr, p.TestName, p.testgrade, 
              'CA Eng. Lang. Dev. Test' as Overall, 'List. & Speaking' as Speak, 'Reading',
                                                         'Writing', 'Listening', 'Speaking', 'Comprehension', 'CELDT Criterion' as CELDT_criterion
  FROM
              (
                     select *
                     from (
                                  select s.id,s.ln,s.fn,s.sc, s.gr
                          , t.id as TestName
                          , (t.gr/10) as testgrade
                          , CONVERT(varchar,t.td,101) as testdate
                          --, t.pt as testpart -- 0 is overall
                          , c.nm as testdesc
                          , t.ss as testscore
                          --, t.ot as profLevel                              
                                  , row_number() over (partition by c.nm,DATEPART(MM, t.td) order by t.td desc) as rn
                                  from tst t
                                  JOIN Stu s ON s.id = t.pid
                                  JOIN ctl c ON t.id = c.id and t.pt = c.pt 
                                  where
                                  t.PID = 2062921 and
                                  s.tg = ' ' and 
                                  t.ID in ( 'CELDT') 
                      ) t2
                     where t2.rn = 1
              ) s
PIVOT
(
  MAX(testscore) FOR testdesc IN ('CA Eng. Lang. Dev. Test', 'List. & Speaking', 'Reading',
                                                         'Writing', 'Listening', 'Speaking', 'Comprehension', 'CELDT Criterion')
  ) p

Answer 1

I think that this is what you want. Remember in the pivot you use the [ and ] to specify the field names, which are really your test names. Also I had to remark out the testpart, because that was not unique, which caused the many rows (from the pivot).

SELECT p.id, p.ln, p.fn, p.sc, p.gr, p.TestName, p.testgrade, 
          [CA Eng. Lang. Dev. Test] as Overall, [List. & Speaking] as Speak, [Reading],
                                                     [Writing], [Listening], [Speaking], [Comprehension], [CELDT Criterion] as CELDT_criterion
FROM
          (
                 select *
                 from (
                              select s.id,s.ln,s.fn,s.sc, s.gr
                              , t.id as TestName
                              , (t.gr/10) as testgrade
                              , CONVERT(varchar,t.td,101) as testdate
                              --, t.pt as testpart -- 0 is overall
                              , c.nm as testdesc
                              , t.ss as testscore
                              --, t.ot as profLevel
                              , row_number() over (partition by c.nm,DATEPART(MM, t.td) order by t.td desc) as rn
                              from tst t
                              JOIN Stu s ON s.id = t.pid
                              JOIN ctl c ON t.id = c.id and t.pt = c.pt 
                              where
                              t.PID = 2062921 and
                              s.tg = ' ' and 
                              t.ID in ( 'CELDT') 
                  ) t2
                 where t2.rn = 1
          ) s
PIVOT
(
  MAX(testscore) FOR testdesc IN ([CA Eng. Lang. Dev. Test], [List. & Speaking], [Reading],
                                                     [Writing], [Listening], [Speaking], [Comprehension], [CELDT Criterion])
 ) p