Group and Separate into Different Columns

Question

I'm trying to group some data and separate one column into several. The following is the kind of table I'm working with although each of these columns are from individual tables connected through an ID on each:

ParticipantId       QuestionText            QuestionAnswer
1               What is your gender?                2
2               What is your gender?                1
3               What is your gender?                1
4               What is your gender?                2
5               What is your gender?                1
1               What is your age?                   28
2               What is your age?                   NULL
3               What is your age?                   55
4               What is your age?                   63


And this is what I want to achieve:  


ParticipantId      Question1Answer    Question2Answer    Question3Answer        
        1               2               28                  3
        2               1               NULL                4

I imagine this is quite a difficult thing to do? As the questionnaire contains around 100 questions.
I don't think using case would be suitable without typing each questionID out. I'm using SQL Server 2008. The following is some of the table structures I'm working with. I'm sure there's an clearer way than typing it out.

The QuestionnaireQuestion table contains QuestionNumber for the sequence and joins to the Question table to via questionID which is the Question tables PID. The question table contains QuestionText and links to the Answer table using QuestionID which contains the answer field. Then the answer table goes through a link table called QuestionnaireInstance which finally links to the PaperQuestionnaire table which contains the ParticipantID.

That probably hasn't made it any clearer, just let me know anything else that might clear it up a bit.

Answer 1

In case you don't want to have to type out all of the question text each time, you could always use this:

;with sample_data as
(
SELECT
ParticipantId
,QuestionText
,QuestionAnswer
,row_number() OVER (PARTITION BY PARTICIPANTID ORDER BY (SELECT NULL)) AS rn
FROM yourdatatable
)
SELECT
PARTICIPANTID
,MAX(CASE WHEN rn = 1 THEN questionanswer END) AS Q1
,MAX(CASE WHEN rn = 2 THEN questionanswer END) AS Q2
,MAX(CASE WHEN rn = 3 THEN questionanswer END) AS Q3
,MAX(CASE WHEN rn = 4 THEN questionanswer END) AS Q4
FROM sample_data
GROUP BY ParticipantId

Although it might be better in your case to consider dynamic pivoting instead, depending on how many columns you want to ultimately end up with

Answer 2

If you have uniquness in you table for column combination ParticipantId and QuestionText then you can use below query also to acive the desired output -

SELECT Participantid,
       MAX(CASE
             WHEN Questiontext = 'What is your gender?' THEN
              Questionanswer
             ELSE
              NULL
           END) AS Question1answer,
       MAX(CASE
             WHEN Questiontext = 'What is your age?' THEN
              Questionanswer
             ELSE
              NULL
           END) AS Question2answer,
       MAX(CASE
             WHEN Questiontext = '...your third question...' THEN
              Questionanswer
             ELSE
              NULL
           END) AS Question3answer,
       .. 
       ..
  FROM Your_Table_Name
 GROUP BY Participantid