title: SQL Server : Pivot with custom column names
body: <p>I need to pivot the Table with custom column name's. Please see the table format below.</p>
  
  <p><strong>Current format</strong>:</p>
  
  <pre><code>ID            question                            Answer
  4482515   I would like to be informed  by mail.   No
  4482515   Plan to Purchase?                       Over 12 months
  4482515   Test Question Text                      some Answer
  </code></pre>
  
  <p>I would like to display the Data in the following format.</p>
  
  <p><strong>Desired format</strong>:</p>
  
  <pre><code>ID       question 1                            Answer1   question 2         Answer 2
  4482515  I would like to be informed  by mail. NO        Plan to Purchase?  Over 12 months 
  </code></pre>
  
  <p>Please note: I don't know about the number of questions and answers in a row so the column's question1 Answer1 should be dynamically generated.</p>
  
  <p>Thank you</p>
  
  <p>Edit: 
  Thank you for your help, I give it a try for the dynamic code you provide it to me and getting this error.</p>
  
  <pre><code>Msg 8167, Level 16, State 1, Line 1
  The type of column "answer" conflicts with the type of other columns specified in the UNPIVOT list.
  </code></pre>
  
  <p>My Table is</p>
  
  <pre><code>RID         Question    Answer
  4482515 Some Question1  Some Answer1
  4482515 Some Question2  Some Answer2
  4482515 Some Question3  Some Answer3
  4484094 Some Question1  Answer1
  4484094 Some Question2  Answer2
  4484094 Some Question3  Answer3
  4484094 Some Question4  Answer4
  </code></pre>
  
  <p>I Print out the SQL and the result is below.</p>
  
  <pre><code>SELECT rid, [question1],[answer1],[question2],[answer2],[question3],[answer3],[question4],[answer4],[question5],[answer5],[question6],[answer6]
                from
                (
                  select rid,
                    col+cast(rn as varchar(10)) col,
                    value
                  from
                  (
                    select rid, question, answer,
                      row_number() over(partition by rid order by rid, question) rn
                    from #tmp_question
                  ) src
                  unpivot
                  (
                    value
                    for col in (question, answer)
                  ) unpiv
                ) d
                pivot 
                (
                    max(value)
                    for col in ([question1],[answer1],[question2],[answer2],[question3],[answer3],[question4],[answer4],[question5],[answer5],[question6],[answer6])
                ) p 
  </code></pre>
  
  <p>My original SQL code is below as well</p>
  
  <pre><code>    DECLARE @cols AS NVARCHAR(MAX),
      @query  AS NVARCHAR(MAX)
  
  select @cols = STUFF((SELECT ',' + QUOTENAME(c.col+cast(rn as varchar(10))) 
                      from 
                      (
                        select row_number() over(partition by rid 
                                                 order by rid, question) rn
                        from #tmp_question
                      ) d
                      cross apply
                      (
                        select 'question' col, 1 sort union all select 'answer', 2
                      ) c
                      group by col, rn, sort
                      order by rn, sort
              FOR XML PATH(''), TYPE
              ).value('.', 'NVARCHAR(MAX)') 
          ,1,1,'')
  
  set @query = 'SELECT rid, ' + @cols + '
                from
                (
                  select rid,
                    col+cast(rn as varchar(10)) col,
                    value
                  from
                  (
                    select rid, question, answer,
                      row_number() over(partition by rid order by rid, question) rn
                    from #tmp_question
                  ) src
                  unpivot
                  (
                    value
                    for col in (question, answer)
                  ) unpiv
                ) d
                pivot 
                (
                    max(value)
                    for col in (' + @cols + ')
                ) p '
  
  --print @query
  execute(@query);
  </code></pre>
  
  <p>Waiting for your kind help!</p>