Combine records using SELECT 1

[bcahillane]

I have a table consisting of answers to survey questions. The survey has 5 questions. My fields are:
surveyID -- Indicates survey #
surQuesID -- Indicates which question is being answered
surAnsID -- Indicates actual answer to question

Sample data looks like:
surveyID surQuesID SurAnsID
100 1 4
100 2 4
100 3 5
100 4 4
100 5 5
101 1 5
101 2 3
101 3 3
101 4 4
101 5 4
102 1 5
etc.

I would like to build a query that shows the results of each individual survey on one line. The output would look like;

Survey Q1 Q2 Q3 Q4 Q5
100 4 4 5 4 5
101 5 3 3 4 4
102 5.....

Suggestions welcomed.

 

[guelphdad]

look into the use of GROUP_CONCAT

 

[r937]

select surveyID
     , max(case when surQuesID = 1
                then SurAnsID end) as Q1
     , max(case when surQuesID = 2
                then SurAnsID end) as Q2
     , max(case when surQuesID = 3
                then SurAnsID end) as Q3
     , max(case when surQuesID = 4
                then SurAnsID end) as Q4
     , max(case when surQuesID = 5
                then SurAnsID end) as Q5
  from daTable
group
    by surveyID

r937.com | rudy.ca