SQL Server using pivot/transpose-CodePudding

I need to transpose/pivot a question and looking for the best approach to do this:

original:

ID	accountno	question	answer
1	111	How old are you?	15
2	111	What is your favorite color?	blue

final result:

ID	accountno	How old are you?	What is your favorite color?
1	111	15	blue

Here is my attempt:

--I only did the first question to see if it would work
select [ID],[accountno], [How old are you?]
from 
(select ID,accountno,question,answer
from table
PIVOT 
(max(answer)
For
question in ([How old are you?])

CodePudding user response：

You were pretty close. Tale a peek at the following

Select *
 From  (
        Select accountno
              ,question
              ,answer
         From YourTable
       ) src 
 Pivot ( max(answer) for question in ( [How old are you?]
                                      ,[What is your favorite color?]
                                     ) ) pvt

Results