Re: Cross tab with Oracle 10g

On Feb 22, 10:25 pm, "antonyliu2..._at_yahoo.com" <antonyliu2..._at_yahoo.com> wrote:
> 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.

Are you sure that this requires a crosstab? The other two answers provided to you are just what is needed if you do in fact need a crosstab. It appears to me that all you need is a simple GROUP BY, unless a counselor has more than one supervisor.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Fri Feb 23 2007 - 14:44:20 CST