Cross tab with Oracle 10g

From: <antonyliu2002_at_yahoo.com>
Date: 22 Feb 2007 19:25:57 -0800
Message-ID: <1172201157.547145.162520@m58g2000cwm.googlegroups.com>

I am not a oracle DBA, I just need to get this straight for a web application I am working on. I know only a little SQL as my major job responsibility is Web development.

We have student counselors, each of them provides counseling service for a bunch of students.

Each student counselor is supervised by one and only one faculty.

I was able to get something like below from two tables through a sql query (blank lines are inserted only for your eyeballing convenience).

Couselor Stu_ID Supervisor

------------- --------- -------------------

tmorris 999239434 pwilliams

waruw 997595754 buchanan
waruw 955557538 buchanan
waruw 992525897 buchanan
waruw 994974599 buchanan
waruw 995599938 buchanan
waruw 992724945 buchanan
waruw 954538288 buchanan

mstuart 992374925 jswenson
mstuart 997733949 jswenson
mstuart 954999558 jswenson
mstuart 995252853 jswenson
mstuart 953929733 jswenson
mstuart 958888249 jswenson

nnlee 994529457 hhall
nnlee 998287535 hhall
nnlee 992737242 hhall
nnlee 954995535 hhall

siemens 993324994 cjohnson
siemens 955554534 cjohnson
siemens 993475597 cjohnson

hpark 993949289 rjackson
hpark 992299445 rjackson
hpark 952292277 rjackson
hpark 993473859 rjackson
hpark 954459555 rjackson

I haven't had good luck in getting a crosstab from this data set. Basically I would like to have something like this:

Counselor Supervisor Num_Of_Stu

---

tmorris pwilliams 1
waruw buchanan 7
mstuart jswenson 6
nnlee hhall 4
siemens cjohnson 3
hpark rjackson 5

In other words, a count of students each student counselor serves. I thought this could be easy, but quite often got a not a group by expression or not a single-group group.

I googled, but could not find detailed tutorials on this. Any hint is highly appreciated. Received on Thu Feb 22 2007 - 21:25:57 CST