Crosstabs help in Cold Fusion

[brettco]

I found one post on here about crosstabs (with a broken link to an article (

http://r937.com/crosstab.cfm),

however some of the information is missing, so let me start from the beginning.

I am trying to group by teacher. Rows will be grade, columns will be course and the cross rows/columns will be number of students in each grade/course. Last column will be total.

i.e. (sorry that the columns do not align)
Teacher
|Math|Science|English|Total
Grade 1| 3 | 2 | 2 | 7
Grade 2| 0 | 1 | 8 | 9

All data is dynamic. The simplified query without any crosstab work done is:

SELECT teacher,course,grade,count(studentid) as studentcount
FROM teachers

Thanks for the help!

BrettCo