Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Cross tab with Oracle 10g

Re: Cross tab with Oracle 10g

From: sybrandb <sybrandb_at_gmail.com>
Date: 23 Feb 2007 01:01:14 -0800
Message-ID: <1172221274.136471.111460@z35g2000cwz.googlegroups.com>


On Feb 23, 4:25 am, "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.

http://asktom.oracle.com
search for crosstab
(Don't tell you didn't know this site)

--
Sybrand Bakker
Senior Oracle DBA
Received on Fri Feb 23 2007 - 03:01:14 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US