title: SQL pivot table on text value
body: <p>I am trying to pivot the following set of data in SQL.</p>
  
  <pre><code>SELECT  ParticipantID,
          ContactReasonTypeID,
          Completed = CASE WHEN Returned IS NOT NULL
                          THEN    'Completed'
                          ELSE 'Not completed'
                      END
  FROM    ContactReason
  WHERE   ContactReasonTypeID = 40 -- M4
  UNION
  SELECT  ParticipantID,
          ContactReasonTypeID,
          Completed = CASE WHEN Returned IS NOT NULL
                          THEN    'Completed'
                          ELSE 'Not completed'
                      END
  FROM    ContactReason
  WHERE   ContactReasonTypeID = 80 -- M8
  </code></pre>
  
  <p>Which gives the following result:</p>
  
  <pre><code>ParticipantID | QuestionnaireID  | Completed
  1               40                 'Completed'
  1               80                 'Not completed'
  2               40                 'Completed'
  2               80                 'Completed'
  3               40                 'Completed'
  3               80                 'Not completed'
  4               40                 'Completed'
  4               80                 'Not completed'
  </code></pre>
  
  <p>What I would like is for the results to be pivoted like so (where M4 is QuestionnaireID 40 and M8 is 80):</p>
  
  <pre><code>ParticipantID | M4           | M8
  1               'Completed'    'Not completed'
  2               'Completed'    'Completed'
  3               'Completed'    'Not completed'
  4               'Completed'    'Not completed'
  </code></pre>
  
  <p>I'm struggling on how to pivot the table. My current SQL is as follows and is resulting in errors. I've tried switching around the columns based on other posts but can't work out what is needed:</p>
  
  <pre><code>SELECT  *
  FROM    (
      SELECT  ParticipantID,
              ContactReasonTypeID,
              Completed = CASE WHEN Returned IS NOT NULL
                              THEN    'Completed'
                              ELSE 'Not completed'
                          END
      FROM    ContactReason
      WHERE   ContactReasonTypeID = 40
      UNION
      SELECT  ParticipantID,
              ContactReasonTypeID,
              Completed = CASE WHEN Returned IS NOT NULL
                              THEN    'Completed'
                              ELSE 'Not completed'
                          END
      FROM    ContactReason
      WHERE   ContactReasonTypeID = 80
  ) AS tbl
  PIVOT (
      MAX(Returned)
      FOR ContactReasonTypeID IN ([M4],[M8])
  ) AS pvt
  ORDER BY ParticipantID
  </code></pre>