Using a Cross-Tab Query

[minckle]

Hi

I’ve got a query working in MS Access and now need to get a similar query working in MySQL. The Access SQL is below, Access calls it a Cross-Tab Query but I don’t know if MySQL supports these functions.

//////////////////////////////////////////////////////
TRANSFORM Count(IIf([contact_ans].[id] Is Null,0,[contact_ans].[id])) AS Id_1
SELECT campaign.name, contact_ans.quest_id, camp_questions.quest_label
FROM (campaign INNER JOIN contact_ans ON campaign.id = contact_ans.camp_id)
INNER JOIN camp_questions ON (contact_ans.quest_id = camp_questions.quest_id)
AND (contact_ans.camp_id = camp_questions.campaign_id)
WHERE (((contact_ans.camp_id)=1))
GROUP BY campaign.name, contact_ans.quest_id, camp_questions.quest_label, contact_ans.camp_id
PIVOT contact_ans.ans_id
//////////////////////////////////////////////////////

My Scenario is: I have
A Campaign table with id, name
A Question table with Id, Campaign_id, Answer1_id, Answer2_id, Answer3_id ( they r multi choice Q’s)
And an Answer table with id Campaign_id, Quest_id, Ans_id

I want a to produce a grid with the results ie.

Ans1 Ans2 Ans3
Q1 3 3 1
Q2 6 4 2
Q3 2 5 1


any advice ???

 

[r937]

no, mysql doesn't do crosstabs the way access does

you have to code for each column yourself

select campaign.name
     , contact_ans.quest_id
     , camp_questions.quest_label
     , sum(case when contact_ans.id = 1 
                then 1 else 0 end)      as Ans1
     , sum(case when contact_ans.id = 2 
                then 1 else 0 end)      as Ans2
     , sum(case when contact_ans.id = 3 
                then 1 else 0 end)      as Ans3
  from campaign 
inner 
  join contact_ans 
    on campaign.id 
     = contact_ans.camp_id
inner 
  join camp_questions 
    on contact_ans.quest_id 
     = camp_questions.quest_id
   and contact_ans.camp_id 
     = camp_questions.campaign_id
 where contact_ans.camp_id = 1
group 
    by campaign.name
     , contact_ans.quest_id
     , camp_questions.quest_label

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[minckle]

HI

Thanks for this, I'll give it go.