Is it possible to construct dynamic aggregate columns in an ARel query that uses a join?

Here's a bit of sample context for my question below to help clarify what I'm asking...

The Schema

Users
- id
- name

Answers
- id
- user_id
- topic_id
- was_correct

Topics
- id
- name

The Data

Users
id | name
1  | Gabe
2  | John

Topics
id | name
1  | Math
2  | English

Answers 
id | user_id | topic_id | was_correct
 1 |       1 |        1 | 0
 2 |       1 |        1 | 1
 3 |       1 |        2 | 1
 4 |       2 |        1 | 0
 5 |       2 |        2 | 0

What I'd like to have, in a result set, is a table with one row per user, and two columns per topic, one that shows the sum of correct answers for the topic, and one that shows the sum of the incorrect answers for that topic. For the sample data above, this result set would look like:

My desired result

users.id | users.name | topic_1_correct_sum | topic_1_incorrect_sum | topic_2_correct_sum | topic_2_incorrect_sum
       1 | Gabe       | 1                   | 1                     | 1                   | 0
       2 | John       | 0                   | 1                     | 0                   | 1

Obvi开发者_JS百科ously, if there were more topics in the Topics table, I'd like this query to include new correct_sum and incorrect_sums for each topic that exists, so I'm looking for a way to write this without hard-coding topic_ids into the sum functions of my select clause.

Is there a smart way to magic this sort of thing with ARel?

Gabe,

What you're looking for here is a crosstab query. There are many approaches to writing this, unfortunately none that will be generic enough in SQL. AFAIK each database handles crosstabs differently. Another way of looking at this is as a "cube", something typically found in OLAP-type databases (as opposed to OLTP).

Its easily writeable in SQL, however will likely include some functions native to the database you're using. What DB are you using?

Your answers table looks like it needs to have 1,2,3,4,5 and not 1,1,1,1,1 as ids...