SQL Server : Pivot with custom column names

Question

I need to pivot the Table with custom column name's. Please see the table format below.

Current format:

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

I would like to display the Data in the following format.

Desired format:

ID       question 1                            Answer1   question 2         Answer 2
4482515  I would like to be informed  by mail. NO        Plan to Purchase?  Over 12 months 

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.

Thank you

Edit: Thank you for your help, I give it a try for the dynamic code you provide it to me and getting this error.

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.

My Table is

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

I Print out the SQL and the result is below.

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 

My original SQL code is below as well

    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);

Waiting for your kind help!

Answer 1

There are a few ways that you can do this.

If you had a known number of questions/answers then you could use row_number() along with an aggregate function and a CASE expression:

select id,
  max(case when rn = 1 then question end) question1,
  max(case when rn = 1 then answer end) answer1,
  max(case when rn = 2 then question end) question2,
  max(case when rn = 2 then answer end) answer2,
  max(case when rn = 3 then question end) question3,
  max(case when rn = 3 then answer end) answer3
from
(
  select id, question, answer,
    row_number() over(partition by id order by id, question) rn
  from yt
) src
group by id;

See SQL Fiddle with Demo

Another suggestion would be to use both the UNPIVOT and the PIVOT function to get the result. The UNPIVOT will take your question and answer columns and convert them into multiple rows.

The basic syntax for the UNPIVOT will be:

select id,
  col+cast(rn as varchar(10)) col,
  value
from
(
  -- when you perform an unpivot the datatypes have to be the same. 
  -- you might have to cast the datatypes in this query
  select id, question, cast(answer as varchar(500)) answer,
    row_number() over(partition by id order by id, question) rn
  from yt
) src
unpivot
(
  value
  for col in (question, answer)
) unpiv;

See Demo. This gives a result:

|      ID |       COL |                                VALUE |
--------------------------------------------------------------
| 4482515 | question1 | I would like to be informed by mail. |
| 4482515 |   answer1 |                                   No |
| 4482515 | question2 |                    Plan to Purchase? |
| 4482515 |   answer2 |                       Over 12 months |
| 4482515 | question3 |                   Test Question Text |
| 4482515 |   answer3 |                          some Answer |

As you can see, I added a row_number() value to the initial subquery so you can associate each answer to the question. Once this has been unpivoted, then you can pivot the result on the new column names with the question/answer with the concatenated row number value. The code with the PIVOT syntax will be:

select id, question1, answer1, question2, answer2,
  question3, answer3
from
(
  select id,
    col+cast(rn as varchar(10)) col,
    value
  from
  (
  -- when you perform an unpivot the datatypes have to be the same. 
  -- you might have to cast the datatypes in this query
    select id, question, cast(answer as varchar(500)) answer,
      row_number() over(partition by id order by id, question) rn
    from yt
  ) src
  unpivot
  (
    value
    for col in (question, answer)
  ) unpiv
) d
pivot
(
  max(value)
  for col in (question1, answer1, question2, answer2,
              question3, answer3)
) piv;

See SQL Fiddle with Demo. Now in your situation, you stated that you will have a dynamic number of questions/answers. If that is the case, then you will need to use dynamic SQL to get the result:

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 id 
                                               order by id, question) rn
                      from yt
                    ) 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 id, ' + @cols + '
              from
              (
                select id,
                  col+cast(rn as varchar(10)) col,
                  value
                from
                (
                 -- when you perform an unpivot the datatypes have to be the same. 
                 -- you might have to cast the datatypes in this query
                  select id, question, cast(answer as varchar(500)) answer,
                    row_number() over(partition by id order by id, question) rn
                  from yt
                ) src
                unpivot
                (
                  value
                  for col in (question, answer)
                ) unpiv
              ) d
              pivot 
              (
                  max(value)
                  for col in (' + @cols + ')
              ) p '

execute(@query);

See SQL Fiddle with Demo. These give a result:

|      ID |                            QUESTION1 | ANSWER1 |         QUESTION2 |        ANSWER2 |          QUESTION3 |     ANSWER3 |
------------------------------------------------------------------------------------------------------------------------------------
| 4482515 | I would like to be informed by mail. |      No | Plan to Purchase? | Over 12 months | Test Question Text | some Answer |