title: SQL Server 2008 Pivot table aggregate function issue
body: <p>I have this query and I am trying to group by surveyname but im getting this error: </p>
  
  <blockquote>
    <p>Msg 8120, Level 16, State 1, Line 1<br>
    Column 'pvt.Follow Up' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.</p>
  </blockquote>
  
  <p>This is the query:</p>
  
  <pre><code>SELECT 
     surveyname, [Follow Up] AS Follow_Up, [Ambiance] AS Ambiance, 
     [Consultation] AS Consultation, [Procedure/Service] AS Procedure_Service
  FROM 
     (SELECT
         s.name surveyname, q.question, subq.answer subquestion,aw.answerweight, 
         aw.score, rc.categoryname, sc.cweight
      FROM survey.dbo.results r
      JOIN survey.dbo.questions q ON r.questionidfk = q.id
      LEFT JOIN survey.dbo.answers subq ON r.itemidfk = subq.id
      LEFT JOIN survey.dbo.answers a ON r.answeridfk = a.id
      JOIN survey.dbo.surveys s ON q.surveyidfk = s.id
      join sigweb.dbo.survey_types_main stm on s.id = stm.surveyidfk
      join survey.dbo.survey_results sr on r.owneridfk = sr.ownerid
      join sigweb.dbo.BosleySurvey bs on bs.contactid = sr.contactid and stm.clientsurveytypeid = bs.surveytype
      join sigweb.dbo.contact c on sr.contactid = c.contactid
      join sigweb.dbo.patient p on p.contactid = c.contactid
      join sigweb.dbo.doctor d on p.doctorid = d.doctorid
      join sigweb.dbo.survey_tracking st on st.contactid = c.contactID and st.surveytypeid = stm.surveytypeid
      left join survey.dbo.answerweighting aw on isnull(r.itemidfk, r.questionidfk) = aw.questionitemidfk and r.answeridfk = aw.answeridfk
      left join survey.dbo.rating_categories rc on aw.categoryidfk = rc.id
      left join survey.dbo.survey_categories sc on aw.categoryidfk = sc.categoryidfk and s.id = sc.surveyidfk
      where 
         aw.answerWeight is not null) ps
  PIVOT
  (
     AVG(score)
     FOR categoryname IN
      ( [Follow Up], [Ambiance], [Consultation], [Procedure/Service])
  ) AS pvt
  </code></pre>
  
  <p>group by surveyname</p>
  
  <p>This is an example of the results im getting</p>
  
  <pre><code>SURVEYNAME      FOLLOW_UP   Ambiance   Consultation   Procedure_Service
  Review             NULL     NULL    NULL          9.81
  Review             9.54     NULL    NULL          NULL
  Consultation       5        NULL    NULL          NULL
  Consultation       NULL     5           NULL          NULL
  Consultation       NULL     5           NULL          NULL
  Consultation       NULL     5           NULL          NULL
  Consultation       NULL     5           NULL          NULL
  Consultation       NULL     5           NULL          NULL
  Consultation       NULL     NULL    5         NULL
  Consultation       5        NULL    NULL          NULL
  Consultation       NULL     NULL    5         NULL
  </code></pre>
  
  <p>This is an example of the data before the pivot:</p>
  
  <pre><code>Review          6   Follow Up
  Review          9   Procedure/Service
  Consultation    5   Ambiance
  Consultation    5   Ambiance
  Consultation    5   Ambiance
  Consultation    5   Ambiance
  Consultation    5   Ambiance
  Consultation    5   Ambiance
  Consultation    5   Consultation
  Consultation    5   Consultation
  </code></pre>
  
  <p>The idea is to group by the <code>surveyname</code> and have only two results in the end.</p>