title: Creating a pivot table with identical values used as columns
body: <p>I have a table that I need to pivot out using SQL Management Studio. This particular example lives on a SQLExpress server, but I have similar tables on a 2008 r2 server that I will also be applying this to.</p>
  
  <p>This table contains the results of a survey (50+ questions and a few hundred GuestID's). Fairly straight forward to pivot, however I've hit a snag in the way that multiple choice questions and answers were entered into the table. </p>
  
  <p>My table looks like this:</p>
  
  <pre><code>GuestID      Question                              Answer
  1            How old are you?                      30
  1            Do you own a car?                     Yes
  1            Why do you own a car?                 Convenience
  1            Why do you own a car?                 To get the girl
  1            Why do you own a car?                 So I can go through the drive-thru
  </code></pre>
  
  <p>I want to pivot to this (... to keep it short):</p>
  
  <pre><code>GuestID      How old..?     Do you own...?    Why do...?    Why do...?    Why do...?
  1            30             Yes               Convenience   To get...     So I can go...
  </code></pre>
  
  <p>Or, ideally, to pivot to this with the answers combined:</p>
  
  <pre><code>GuestID      How old..?     Do you own...?    Why do...?
  1            30             Yes               Convenience, To get..., So I can...
  </code></pre>
  
  <p>The statement I'm currently working with is this:</p>
  
  <pre><code>declare @QuestionList as varchar(max)
  select @QuestionList = 
      stuff(
      (
          select DISTINCT ',[' + RIGHT(Question,128) + ']'
          from AnnualSurveyAnswers
          where GuestId = 43
          for xml path ('')
          ),1,1,'')
  
  declare @dynamic_PQ as varchar(max)
  set @dynamic_PQ = 'select [GuestID], ' + @QuestionList + 
      'from
      (
          Select [GuestID],RIGHT([Question],128)AS Question,[Answer]
          From dbo.AnnualSurveyAnswers
      ) as S
  
      PIVOT
  
      (
          MAX([Answer])
          for Question IN (' + @QuestionList + ')
      ) as P
      '
  Exec(@dynamic_PQ)
  </code></pre>
  
  <p>Results:</p>
  
  <pre><code>GuestID     How old...?     Do you own...?     Why do you...?
  1           30              Yes                Convenience
  2           35              Yes                To get the girl
  </code></pre>
  
  <p>I had to add <code>DISTINCT</code> to <code>@QuestionList</code> because I received an error about the column being specified multiple times. Makes sense. We can't have multiple columns with the same name. However, with <code>DISTINCT</code>, it now only displays one of the multiple choice answers. I also added <code>MAX</code> to cover the aggregate requirement over <code>[Answer]</code>, but maybe I should use something different?</p>
  
  <p>There are multiple, multiple choice questions and they may contain 1 response or 8 responses. Are there any options other than altering the original data? </p>